﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.Collections.Generic;
using System.Text;

using System.Web;
using CrystalDecisions.CrystalReports.Engine;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using CrystalDecisions.Shared;

using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using Microsoft.Office.Core;
using System.Reflection;
using System.Text.RegularExpressions;


namespace ReportLibrary
{
    
    public class JasService
    {
        private bool Authorized = true;
        string _DocPath = Properties.Settings.Default.DocPath;
        //string _TempDocPath = Properties.Settings.Default.TempDocPath;
        string _TempDocPath = Properties.Settings.Default.DocPath;
        string _ReportLocation = Properties.Settings.Default.ReportLocation;
        string _DocumentURL = Properties.Settings.Default.DocumentUrl;
        string _TempDocumentURL = Properties.Settings.Default.TempDocumentUrl;


        #region properties
        private string NotAuthorized()
        {
            return "<Root><Message>Not Authorized</Message></Root>";
        }

        private string TrueMessage()
        {
            return "<Root><Message>True</Message></Root>";
        }

        private string FalseMessage()
        {
            return "<Root><Message>False</Message></Root>";
        }
        // Add more operations here and mark them with 


        public string GetReportLocation()
        {

            return _ReportLocation;
        }

        public string GetDocumentURL()
        {

            return _DocumentURL;
        }

        public string GetWSPath()
        {

            return _DocPath;
        }


        #endregion


        public DataTable GetDataForSymmonsReport()
        {
            DataTable dtRet = new DataTable();
            SqlConnection Conn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            try
            {

                SqlCommand command = new SqlCommand("GetDataForSymmonsReport", Conn);
                command.CommandType = CommandType.StoredProcedure;

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                //using (command.Connection)
                //{
                //command.Connection.Open();
                adapter.Fill(dtRet);
                //}
                //command.Connection.Close();



                return dtRet;

            }
            catch (Exception)
            {
                return null;
            }
        }


        // Raphael 07/06/2012 - BGIReport Demmand
        public DataTable GetDatesForBGInt(string login)
        {
            DataTable dtRet = new DataTable();
            SqlConnection Conn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            try
            {
                SqlCommand command = new SqlCommand("SELECT startDate, EndDate FROM TBL_DailyShipmentReport WHERE StartDate Is Not Null AND loginId = '" + login + "' AND AcctId = '2314' ", Conn);
                command.CommandType = CommandType.Text;

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dtRet);
                return dtRet;
            }
            catch (Exception)
            {
                return null;
            }
        }


        // Raphael 07/06/2012 - BGIReport Demmand
        public string[] GenDailyShipmentReportBGInt(string[] InfoIn, String dtReport, string email, out bool emptyReport)
        {
            string[] szReturn_Array = new string[6];
            string myExportFile = string.Empty;
            string error = string.Empty;
            string reportName = string.Empty;
            string fileName = string.Empty;

            Excel.Application oExcel = new Excel.Application();
            Excel.Workbooks oBooks;
            Excel.Workbook oBook;
            Excel.Sheets oSheets;
            Excel.Worksheet oSheet;
            Excel.Range oCells;
            object misValue = System.Reflection.Missing.Value;



            try
            {
                // Raphael BGINT 18/06/2012
                DateTime dtStart = DateTime.MinValue;
                DateTime? dtEnd = DateTime.Now;
                DataTable dtDates = GetDatesForBGInt(email);
                DataTable data = new DataTable();
                try
                {
                    if (dtDates != null && dtDates.Rows.Count > 0 && dtDates.Rows[0]["startDate"] != DBNull.Value)
                    {
                        dtStart = Convert.ToDateTime(dtDates.Rows[0]["startDate"]);
                        dtEnd = dtDates.Rows[0]["endDate"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(dtDates.Rows[0]["endDate"]) : DateTime.Now;
                        data = GetDataForBGIntReport(dtStart, dtEnd);
                    }
                    else
                        data = GetDataForBGIntReport(null, null);

                }
                catch (Exception)
                {
                    data = GetDataForBGIntReport(null, null);
                }

                if (data != null && data.Rows.Count > 0)
                {
                    string szDoc = string.Empty;
                    fileName = "BG_Tanzania_Tracking_Report_" + dtReport;


                    reportName = _ReportLocation + "BGInt.xlsx";


                    oExcel.Visible = false;
                    oExcel.DisplayAlerts = false;

                    //Start a new workbook

                    oBooks = oExcel.Workbooks;
                    oBook = oBooks.Open(reportName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
                    oSheets = oBook.Worksheets;
                    oSheet = (Excel.Worksheet)oSheets.get_Item(1);
                    oCells = oSheet.Cells;
                    DumpDataForBGIntRpt(data, oCells); //Fill in the data

                    fileName = fileName + ".xlsx";
                    myExportFile = _DocPath + fileName;

                    oSheet.SaveAs(myExportFile, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); //Save in a temporary file
                    oBook.Saved = true;
                    oBook.Close(true, misValue, misValue);
                    emptyReport = false;

                }
                else
                {
                    emptyReport = true;
                }



            }
            catch (Exception ex)
            {
                error = "(BGInt)" + ex.Message;
                emptyReport = true;
            }
            finally
            {

                //Quit Excel and thoroughly deallocate everything
                oExcel.Quit();
                //releaseObject(oSheet);
                //releaseObject(oBook);
                releaseObject(oExcel);

                szReturn_Array[0] = error;
                //szReturn_Array[1] = szReportPathName; VERIFICAR ISSO AQUI
                szReturn_Array[2] = myExportFile;
            }
            return szReturn_Array;
        }


        // Raphael 07/06/2012 - BGIReport Demmand
        private void DumpDataForBGIntRpt(DataTable dt, Excel.Range oCells)
        {


            DataRow dr;
            oCells[4, 3] = "Last Updated " + DateTime.Now.ToString("dd.MM.yyyy");
            for (int i = 0; i <= (dt.Rows.Count - 1); i++)
            {
                dr = dt.Rows[i];
                bool isDelivered = false;
                if (dr["Delivery to Site Format"] != DBNull.Value)
                    isDelivered = true;

                for (int j = 0; j <= (dt.Columns.Count - 1); j++)
                {
                    oCells[i + 7, j + 1] = dr[j].ToString();

                    // Raphael BGINT 18/06/2012
                    ((Excel.Range)(oCells[i + 7, j + 1])).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                    ((Excel.Range)(oCells[i + 7, j + 1])).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(0, 0, 0));
                    if (isDelivered)
                    {
                        ((Excel.Range)(oCells[i + 7, j + 1])).Interior.Color =
                            System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(197, 217, 241));
                    }
                }


            }
        }

        // Raphael 07/06/2012 - BGIReport Demmand
        private DataTable GetDataForBGIntReport(DateTime? startDate, DateTime? endDate)
        {
            DataTable dtRet = new DataTable();
            SqlConnection Conn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            try
            {

                SqlCommand command = new SqlCommand("GetDataForBGIReport", Conn);
                command.CommandType = CommandType.StoredProcedure;
                if (startDate.HasValue)
                    command.Parameters.Add("@startDate", SqlDbType.DateTime).Value = startDate.Value;

                if (endDate.HasValue)
                    command.Parameters.Add("@endDate", SqlDbType.DateTime).Value = endDate.Value;

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                //using (command.Connection)
                //{
                //command.Connection.Open();
                adapter.Fill(dtRet);
                //}
                //command.Connection.Close();



                return dtRet;

            }
            catch (Exception)
            {
                return null;
            }
        }

        public string[] GenDailyShipmentReportSymmons(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];
            string myExportFile = string.Empty;
            string error = string.Empty;
            string reportName = string.Empty;
            string fileName = string.Empty;

            Excel.Application oExcel = new Excel.Application();
            Excel.Workbooks oBooks;
            Excel.Workbook oBook;
            Excel.Sheets oSheets;
            Excel.Worksheet oSheet;
            Excel.Range oCells;
            object misValue = System.Reflection.Missing.Value;



            try
            {

                DataTable data = GetDataForSymmonsReport();
                string szDoc = string.Empty;
                fileName = "DailyShipRpt" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                fileName = System.Text.RegularExpressions.Regex.Replace(fileName, "/", "");
                fileName = System.Text.RegularExpressions.Regex.Replace(fileName, " ", "");
                fileName = System.Text.RegularExpressions.Regex.Replace(fileName, ":", "");

                reportName = _ReportLocation + "Symmons.xls";


                oExcel.Visible = false;
                oExcel.DisplayAlerts = false;

                //Start a new workbook

                oBooks = oExcel.Workbooks;
                oBook = oBooks.Open(reportName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
                oSheets = oBook.Worksheets;
                oSheet = (Excel.Worksheet)oSheets.get_Item(1);
                oCells = oSheet.Cells;
                DumpDataForGatesRpt(data, oCells); //Fill in the data

                fileName = fileName + ".xls";
                myExportFile = _DocPath + fileName;

                oSheet.SaveAs(myExportFile, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); //Save in a temporary file
                oBook.Saved = true;
                oBook.Close(true, misValue, misValue);


            }
            catch (Exception ex)
            {
                error = "(Symmons)" + ex.Message;
            }
            finally
            {

                //Quit Excel and thoroughly deallocate everything
                oExcel.Quit();
                //releaseObject(oSheet);
                //releaseObject(oBook);
                releaseObject(oExcel);

                szReturn_Array[0] = error;
                //szReturn_Array[1] = szReportPathName; VERIFICAR ISSO AQUI
                szReturn_Array[2] = myExportFile;
            }
            return szReturn_Array;
        }



        public String[] VerifyAccountId(String id)
        {
            DataSet dsData = new DataSet();
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = Properties.Settings.Default.ConnectionString;

            String[] str = new String[2];

            try
            {
                SqlCommand command = new SqlCommand("Select AcctId, StartDate From TBL_DailyShipmentReport Where id = @id", Conn);
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("@id", id);


                using (command.Connection)
                {
                    try
                    {
                        command.Connection.Open();
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                str[0] = reader[0] != DBNull.Value ? reader[0].ToString() : null;
                                str[1] = reader[1] != DBNull.Value ? "true" : "false";
                            }

                        }

                    }
                    finally
                    {
                        if (command.Connection != null)
                        {
                            command.Connection.Close();
                            command.Connection.Dispose();
                        }
                    }
                }


            }
            catch (Exception)
            {
                return null;
            }
            return str;

        }

        public string[] GenInvaCareReport()
        {


            string[] szReturn_Array = new string[6];
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string myexportfile = string.Empty;
            string CDMFile = string.Empty;
            string DailyShipmentfile = string.Empty;
            string szError = string.Empty;
            string strFilename = string.Empty;
            DataSet dsMainOut = null;
            string file = string.Empty;

            try
            {


                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];

                GetDataForInvaCareReport(szConnStr, ref dsMainOut);

                if (dsMainOut.Tables.Count > 0)
                {

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        // For CDM Report
                        szError = string.Empty;

                        strFilename = "CDMRpt" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");

                        file = WriteToString(dsMainOut.Tables[0], true, true);

                        strFilename = strFilename + ".csv";
                        CDMFile = _DocPath + strFilename;

                        File.WriteAllText(CDMFile, file.ToString());

                    }

                    if (dsMainOut.Tables[1].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        //For Daily Shipment Report
                        szError = string.Empty;
                        strFilename = string.Empty;
                        strFilename = "DSRpt" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");

                        file = WriteToString(dsMainOut.Tables[1], true, true);

                        strFilename = strFilename + ".csv";
                        DailyShipmentfile = _DocPath + strFilename;

                        File.WriteAllText(DailyShipmentfile, file.ToString());

                    }

                    myexportfile = CDMFile + "|" + DailyShipmentfile;
                }
                else
                {
                    szError = "No Records Found";
                }
            }
            catch (Exception ex)
            {
                szError = "(GenInvaCareReport) " + ex.Message;
            }
            finally
            {

                szReturn_Array[0] = szError;
                szReturn_Array[2] = myexportfile;
                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        //Get data for InvaCare report 
        private void GetDataForInvaCareReport(string szConnStr, ref DataSet dsOut)
        {

            DataSet dsData = new DataSet();
            SqlConnection Conn = new SqlConnection(szConnStr);

            try
            {
                SqlCommand command = new SqlCommand("spInvaCareReport", Conn);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = 250;
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dsData);
                dsOut = dsData;

            }
            catch (Exception Ex)
            {
                System.Diagnostics.Debug.WriteLine("Error In GetDataForInvaCareReport -->" + Ex.Message);

            }
            finally
            {
                Conn.Close();
            }

        }

        private string WriteToString(DataTable table, bool header, bool quoteall)
        {
            StringWriter writer = new StringWriter();

            WriteToStream(writer, table, header, quoteall);
            return writer.ToString();
        }

        private void WriteToStream(TextWriter stream, DataTable table, bool header, bool quoteall)
        {

            //Write Column name
            if (header)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    WriteItem(stream, table.Columns[i].Caption, quoteall);
                    if (i < table.Columns.Count - 1)
                        stream.Write(',');
                    else
                        stream.Write(stream.NewLine);

                }

            }

            //Write data

            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    WriteItem(stream, row[i], quoteall);

                    if (i < table.Columns.Count - 1)
                        stream.Write(',');
                    else
                        stream.Write(stream.NewLine);
                }
            }

            stream.Close();
        }

        private void WriteItem(TextWriter stream, object item, bool quoteall)
        {
            if (item == null)
                return;
            string s = item.ToString();
            stream.Write(s);
        }


        public string[] GenerateSA_TFG_XML(string sAcctId, string sType, int sFileId)
        {
            string[] szReturn_Array = new string[6];
            string szError = string.Empty;
            string strXml = string.Empty;
            string strfilename = string.Empty;
            string szConnStr = Properties.Settings.Default.ConStrJasFilesDB;
            StreamWriter objReader;
            string myexportfile = string.Empty;

            DataSet dsData = new DataSet();
            SqlConnection Conn = new SqlConnection(szConnStr);

            try
            {
                SqlCommand command = new SqlCommand("uspFILOUTGetFiles", Conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Acct", sAcctId);
                command.Parameters.AddWithValue("@Type", sType);
                command.Parameters.AddWithValue("@Fileid", sFileId);


                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dsData);


                if (dsData.Tables.Count > 0)
                {

                    if (dsData.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {

                        strXml = dsData.Tables[0].Rows[0]["FileData"].ToString();
                        strfilename = dsData.Tables[0].Rows[0]["FileName"].ToString();

                        //Create File 

                        myexportfile = _DocPath + strfilename;
                        objReader = new StreamWriter(myexportfile, true);
                        objReader.Write(strXml);
                        objReader.Close();
                    }
                }

            }
            catch (Exception Ex)
            {
                System.Diagnostics.Debug.WriteLine("Error In GenerateSA_TFG_XML -->" + Ex.Message);

            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[2] = myexportfile;
                if (!(dsData == null))
                {
                    dsData.Dispose();
                }
                Conn.Close();
            }

            return szReturn_Array;

        }

       
        public string[] GenSSRReport(string strAccount, string strType, string strFileId)
        {

            string[] szReturn_Array = new string[6];
            string szConnStr = Properties.Settings.Default.ConStrJasFilesDB;
            string myexportfile = string.Empty;
            string SSRFile = string.Empty;
            string szError = string.Empty;
            string strFilename = string.Empty;
            DataSet dsMainOut = null;
            string file = string.Empty;
            string _DocPath = string.Empty;

            try
            {


                GetDataForSSRReport(szConnStr, strAccount, strType, strFileId, ref dsMainOut);

                if (dsMainOut.Tables.Count > 0)
                {

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {

                        szError = string.Empty;
                        strFilename = dsMainOut.Tables[0].Rows[0]["FileName"].ToString();
                        file = dsMainOut.Tables[0].Rows[0]["FileData"].ToString();
                        _DocPath = dsMainOut.Tables[0].Rows[0]["FilePath"].ToString();
                        SSRFile = _DocPath + strFilename;
                        File.WriteAllText(SSRFile, file.ToString());

                    }

                    myexportfile = SSRFile;
                }
                else
                {
                    szError = "No Records Found";
                }
            }
            catch (Exception ex)
            {
                szError = "(GenSSRReport) " + ex.Message;
            }
            finally
            {

                szReturn_Array[0] = szError;
                szReturn_Array[2] = myexportfile;
                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }


        private void GetDataForSSRReport(string szConnStr, string sAcctId, string sType, string strFileId, ref DataSet dsOut)
        {

            DataSet dsData = new DataSet();
            SqlConnection Conn = new SqlConnection(szConnStr);

            try
            {
                SqlCommand command = new SqlCommand("uspFILOUTGetFiles", Conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Acct", sAcctId);
                command.Parameters.AddWithValue("@Type", sType);
                command.Parameters.AddWithValue("@Fileid", strFileId);


                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dsData);
                dsOut = dsData;

            }
            catch (Exception Ex)
            {
                System.Diagnostics.Debug.WriteLine("Error In GetDataForSSRReport -->" + Ex.Message);

            }
            finally
            {
                Conn.Close();
            }

        }


        public string[] GenSIReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];


            string szLoginID = String.Empty;
            string szKey = String.Empty;
            string szType = String.Empty;

            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;

            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;


            try
            {

                if ((InfoIn.Length - 1) > 0)
                {
                    szKey = InfoIn[0];
                    szType = InfoIn[1];
                }

                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];

                //szReadInfo[0] = szLoginID;
                szReadInfo[0] = szKey;
                szReadInfo[1] = szType;

                szError = GetDataAsSIRpt(szConnStr, "spRptSI", szReadInfo, "SI", ref dsMainOut);
                if (szError == string.Empty)
                {

                    rpt = new ReportDocument();
                    strFilename = "SI" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");

                    //string szPath = GetWSPath();
                    report_name = _ReportLocation + "SI.rpt"; //"BOL.rpt";
                    rpt.Load(report_name);
                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);
                        rpt.Subreports["ContainerInfo.rpt"].SetDataSource(dsMainOut.Tables[1]);
                        rpt.Subreports["Shipper"].SetDataSource(dsMainOut.Tables[2]);
                        rpt.Subreports["ControlTot.rpt"].SetDataSource(dsMainOut.Tables[3]);
                        rpt.Subreports["PrintInstructions.rpt"].SetDataSource(dsMainOut.Tables[4]);

                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();

                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                        rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                        strFilename = strFilename + ".pdf";


                        myexportfile = _DocPath + strFilename;

                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        //myexportfile = _DocumentURL + strFilename;

                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_BOL) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = _DocumentURL + strFilename;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }
        public string[] GenGatesTaiwanRecReport(string InfoIn)
        {
            string[] szReturn_Array = new string[6];


            string szBegdate = string.Empty;
            string szEndDate = string.Empty;

            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;

            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;

            Excel.Application oExcel = new Excel.Application();
            Excel.Workbooks oBooks;
            Excel.Workbook oBook;
            Excel.Sheets oSheets;
            Excel.Worksheet oSheet;
            Excel.Range oCells;
            object misValue = System.Reflection.Missing.Value;



            try
            {

                string szDoc = string.Empty;

                szError = GetDataAsCursorGatesTwnRecRpt(szConnStr, "sp_getinfo_whse_excel", InfoIn, ref dsMainOut);
                if (szError == string.Empty)
                {


                    strFilename = "Gates_TWN_Rec" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");

                    report_name = _ReportLocation + "GatesTaiwanRec.xls"; // @"D:\etrack_net\JasOrbisSL\wsJas\Reports\GatesTaiwanRec.xls";

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {

                        oExcel.Visible = false;
                        oExcel.DisplayAlerts = false;

                        //Start a new workbook

                        oBooks = oExcel.Workbooks;
                        oBook = oBooks.Open(report_name, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
                        oSheets = oBook.Worksheets;
                        oSheet = (Excel.Worksheet)oSheets.get_Item(1);
                        oCells = oSheet.Cells;
                        DumpDataForGatesRpt(dsMainOut.Tables[0], oCells); //Fill in the data

                        strFilename = strFilename + ".xls";
                        myexportfile = _DocPath + strFilename;

                        oSheet.SaveAs(myexportfile, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); //Save in a temporary file
                        oBook.Saved = true;
                        oBook.Close(true, misValue, misValue);

                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_GatesTaiwanRep) " + ex.Message;
            }
            finally
            {

                //Quit Excel and thoroughly deallocate everything
                oExcel.Quit();
                //releaseObject(oSheet);
                //releaseObject(oBook);
                releaseObject(oExcel);

                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        public string[] GenGatesPOINQReport(string InfoIn)
        {
            string[] szReturn_Array = new string[6];


            string szBegdate = string.Empty;
            string szEndDate = string.Empty;

            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;

            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;

            Excel.Application oExcel = new Excel.Application();
            Excel.Workbooks oBooks;
            Excel.Workbook oBook;
            Excel.Sheets oSheets;
            Excel.Worksheet oSheet;
            Excel.Range oCells;
            object misValue = System.Reflection.Missing.Value;



            try
            {

                string szDoc = string.Empty;

                szError = GetDataAsCursorGatesPOINQRpt(szConnStr, "C_PODetailInquiry_Fetch", InfoIn, ref dsMainOut);
                if (szError == string.Empty)
                {


                    strFilename = "Gates_POINQ" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");

                    report_name = _ReportLocation + "NightlyRep.xls"; // @"D:\etrack_net\JasOrbisSL\wsJas\Reports\NightlyRep.xls";

                    if (dsMainOut.Tables[1].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {

                        oExcel.Visible = false;
                        oExcel.DisplayAlerts = false;

                        //Start a new workbook

                        oBooks = oExcel.Workbooks;
                        oBook = oBooks.Open(report_name, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
                        oSheets = oBook.Worksheets;
                        oSheet = (Excel.Worksheet)oSheets.get_Item(1);
                        oCells = oSheet.Cells;
                        DumpDataForGatesInqRpt(dsMainOut.Tables[1], oCells); //Fill in the data

                        strFilename = strFilename + ".xls";
                        myexportfile = _DocPath + strFilename;

                        oSheet.SaveAs(myexportfile, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); //Save in a temporary file
                        oBook.Saved = true;
                        oBook.Close(true, misValue, misValue);

                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_GatesPOINQRep) " + ex.Message;
            }
            finally
            {

                //Quit Excel and thoroughly deallocate everything
                oExcel.Quit();
                //releaseObject(oSheet);
                //releaseObject(oBook);
                releaseObject(oExcel);

                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }
 public string[] GenDailyShipmentReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];


            string szLoginID = String.Empty;
            string szKey = String.Empty;
            string szType = String.Empty;

            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;

            int columnNo = 0;
            int ShipStatusColNo = 0;
            int rowNo = 13;

            int DocsColNo = 0;
            string[] DocsList;

            // Raphael 9/9/2013 - Verify if a spreadsheet with same exact parameters has already been generated
            try
            {
                String excelName = CheckSameParameterExcelSheet(InfoIn[0], szConnStr);
                if (excelName != null)
                {
                    // In this case, there is already a spreadsheet with the same parameters, 
                    // no need to generate a new one
                    
                    szReturn_Array[0] = szError;
                    szReturn_Array[1] = _DocumentURL + excelName;
                    szReturn_Array[2] = _DocPath + excelName;

                    return szReturn_Array;
                }
            }
            catch (Exception)
            {
                // Do nothing
            }


            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range workSheet_range = null;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);


            try
            {


                if ((InfoIn.Length - 1) > 0)
                {
                    szKey = InfoIn[0];
                    szType = InfoIn[1];
                }

                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];

                szReadInfo[0] = szKey;
                szReadInfo[1] = szType;

                DataSet dsColumns = GetDailyShipmentRptColsForLoginId(szKey, szConnStr);

                strFilename = "DailyShipRpt" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");

                GetDataForDailyShipmentRpt(szReadInfo[0].ToString(), szConnStr, ref dsMainOut, dsColumns);

                if (dsMainOut.Tables.Count > 0)
                {

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {

                        //Add Image
                        string _stPicture = Properties.Settings.Default.ImagePath; //"D:\\etrack_net\\JasOrbisSL\\wsJas\\jasorbis1.png";


                        xlWorkSheet.Shapes.AddPicture(_stPicture, MsoTriState.msoFalse, MsoTriState.msoCTrue, 200, 10, 150, 50);



                        //Excel.Range oRange = (Excel.Range)xlWorkSheet.Cells[1, 9];
                        //Image oImage = Image.FromFile(_stPicture);
                        //System.Web.Forms.Clipboard.SetDataObject(oImage, true);
                        //xlWorkSheet.Paste(oRange, _stPicture);

                        //Add Report Date

                        xlWorkSheet.Cells[6, 1] = "Date: " + DateTime.Now.ToString("MM/dd/yyyy");
                        workSheet_range = (Excel.Range)xlWorkSheet.Cells[6, 9];
                        workSheet_range.Font.Bold = true;
                        //Add Report Title

                        xlWorkSheet.Cells[6, 5] = "Daily Shipment Report";
                        workSheet_range = (Excel.Range)xlWorkSheet.Cells[6, 5];
                        workSheet_range.Font.Bold = true;


                        foreach (DataRow Cols in dsColumns.Tables[0].Rows)
                        {
                            columnNo++;



                            if (Cols["DisplayName"].ToString() == "Documents")
                            {
                                DocsColNo = columnNo;
                            }
                            if (Cols["DisplayName"].ToString() == "Shipment Status")
                            {
                                ShipStatusColNo = columnNo;

                                xlWorkSheet.Cells[8, 1] = "Legend";
                                workSheet_range = (Excel.Range)xlWorkSheet.Cells[8, 1];
                                workSheet_range.Font.Bold = true;

                                xlWorkSheet.Cells[9, 1] = "Arrived";
                                workSheet_range = (Excel.Range)xlWorkSheet.Cells[9, 1];
                                workSheet_range.Font.Bold = true;

                                workSheet_range = (Excel.Range)xlWorkSheet.Cells[9, 2];
                                workSheet_range.Interior.ColorIndex = 20;

                                xlWorkSheet.Cells[9, 3] = "Departure";
                                workSheet_range = (Excel.Range)xlWorkSheet.Cells[9, 3];
                                workSheet_range.Font.Bold = true;

                                workSheet_range = (Excel.Range)xlWorkSheet.Cells[9, 4];
                                workSheet_range.Interior.ColorIndex = 40;

                                xlWorkSheet.Cells[9, 5] = "Delivered";
                                workSheet_range = (Excel.Range)xlWorkSheet.Cells[9, 5];
                                workSheet_range.Font.Bold = true;

                                workSheet_range = (Excel.Range)xlWorkSheet.Cells[9, 6];
                                workSheet_range.Interior.ColorIndex = 35;

                                xlWorkSheet.Cells[9, 7] = "PreDeparted";
                                workSheet_range = (Excel.Range)xlWorkSheet.Cells[9, 7];
                                workSheet_range.Font.Bold = true;

                                xlWorkSheet.Cells[9, 9] = "Customs";
                                workSheet_range = (Excel.Range)xlWorkSheet.Cells[9, 9];
                                workSheet_range.Font.Bold = true;

                                workSheet_range = (Excel.Range)xlWorkSheet.Cells[9, 10];
                                workSheet_range.Interior.ColorIndex = 15;



                            }


                            xlWorkSheet.Cells[12, columnNo] = Cols["DisplayName"].ToString();
                            workSheet_range = (Excel.Range)xlWorkSheet.Cells[12, columnNo];
                            workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb();
                            workSheet_range.Font.Bold = true;
                            workSheet_range.ColumnWidth = Cols["DisplayLength"];

                        }
                        

                        char[] delimiters = new char[] { '\r' };

                        foreach (DataRow row in dsMainOut.Tables[0].Rows)
                        {
                            //rowNo++; //Feb 15 2012 Ticket#19459 Daily shipment report . It has been commented due to remove blank row after column title.

                            for (int i = 1; i <= dsColumns.Tables[0].Rows.Count; i++)
                            {



                                if (DocsColNo == i)
                                {
                                    DocsList = row["Column" + i.ToString()].ToString().Split(delimiters);
                                    int CellR = rowNo;

                                    for (int j = 0; j < DocsList.Length; j++)
                                    {

                                            xlWorkSheet.Cells[CellR, i] = DocsList[j].ToString();
                                            workSheet_range = (Excel.Range)xlWorkSheet.Cells[CellR, i];
                                            workSheet_range.WrapText = true;
                                            workSheet_range.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;

                                       
                                        if (ShipStatusColNo != 0)
                                        {
                                            if (row["Column" + ShipStatusColNo.ToString()].ToString() == "Departure")
                                            {
                                                workSheet_range.EntireRow.Interior.ColorIndex = 40;
                                            }
                                            else if (row["Column" + ShipStatusColNo.ToString()].ToString() == "Arrived")
                                            {
                                                workSheet_range.EntireRow.Interior.ColorIndex = 20;
                                            }
                                            else if (row["Column" + ShipStatusColNo.ToString()].ToString() == "Delivered")
                                            {
                                                workSheet_range.EntireRow.Interior.ColorIndex = 35;
                                            }
                                            else if (row["Column" + ShipStatusColNo.ToString()].ToString() == "Customs")
                                            {
                                                workSheet_range.EntireRow.Interior.ColorIndex = 15;
                                            }

                                        }
                                        rowNo = CellR;
                                        CellR++;
                                    }

                                }
                                else
                                {   
                                    workSheet_range = (Excel.Range)xlWorkSheet.Cells[rowNo, i];

                                    //2013 Aug 22rd TW: added to format the way this column below display data in the cell.
                                    List<string> formatColumns = new List<string> { "PO#", "Packing List#", "Part #", "Track#", "Customer Ref#", "Invoice#", "Part Description" };
                                    if (formatColumns.Contains(dsColumns.Tables[0].Rows[i-1].ItemArray[2].ToString()))
                                    {
                                       
                                        workSheet_range.NumberFormat = "@";
                                        xlWorkSheet.Cells[rowNo, i] = string.IsNullOrEmpty(row["Column" + i.ToString()].ToString()) 
                                            ? row["Column" + i.ToString()].ToString() 
                                            : row["Column" + i.ToString()].ToString().Replace(",",   Environment.NewLine);
                                    }
                                    else
                                    {
                                        xlWorkSheet.Cells[rowNo, i] = row["Column" + i.ToString()].ToString();
                                    }
                                   
                                    workSheet_range.WrapText = true;
                                    workSheet_range.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;

                                    if (ShipStatusColNo == i)
                                    {
                                        if (row["Column" + i.ToString()].ToString() == "Departure")
                                        {
                                            workSheet_range.EntireRow.Interior.ColorIndex = 40;
                                        }
                                        else if (row["Column" + i.ToString()].ToString() == "Arrived")
                                        {
                                            workSheet_range.EntireRow.Interior.ColorIndex = 20;
                                        }
                                        else if (row["Column" + i.ToString()].ToString() == "Delivered")
                                        {
                                            workSheet_range.EntireRow.Interior.ColorIndex = 35;
                                        }
                                        else if (row["Column" + i.ToString()].ToString() == "Customs")
                                        {
                                            workSheet_range.EntireRow.Interior.ColorIndex = 15;
                                        }

                                    }

                                }


                            }

                            rowNo++; //Feb 15 2012 Ticket#19459 Daily shipment report . blank row be removed after column title

                    
                        }


                        strFilename = strFilename + ".xls";

                        myexportfile = _DocPath + strFilename;

                        //Kavita 11/01/2011 . In order to surpress the prompt window
                        xlApp.Visible = false;
                        xlApp.DisplayAlerts = false;
                        //end 11/01/2011

                        xlWorkBook.SaveAs(myexportfile, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                        xlWorkBook.Saved = true;
                        xlWorkBook.Close(true, misValue, misValue);

                        // Raphael 9/9/2013 - Save the parameters to be compared in future generation of spreadsheets
                        SaveSpreadSheetParameterData(strFilename, szKey, szConnStr);
                       

                    }
                }
                else
                {
                    szError = "No Records Found";
                }

            }
            catch (Exception ex)
            {
                szError = "(Generate_DailyShipment) " + ex.Message;
            }
            finally
            {
                //Kavita 11/02/01 to supress save prompt window 

                if (szError != "")
                {
                    xlApp.Visible = false;
                    xlApp.DisplayAlerts = false;
                    xlWorkBook.Saved = false;
                }
                //end kavita 11/02/01

                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                szReturn_Array[0] = szError;
                szReturn_Array[1] = _DocumentURL + strFilename;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        private String CheckSameParameterExcelSheet(String parId, string coString)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(coString);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "CheckSameParameterExcelSheet";
            try
            {
                Guid g = new Guid(parId);

                using (cmd.Connection)
                {
                    cmd.Parameters.Add(new SqlParameter("@PARAMETERID", SqlDbType.UniqueIdentifier));
                    cmd.Parameters[0].Value = g;
                    cmd.Connection.Open();
                    Object ret = cmd.ExecuteScalar();

                    if (ret != null)
                        return ret.ToString();
                    else
                        return null;

                }
            }
            catch (Exception)
            {

            }
            return null;
        }

        private void SaveSpreadSheetParameterData(String sheetName, String parId, string coString)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(coString);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "INSERTSpreadsheetParameterComparer";
            try
            {
                Guid g = new Guid(parId);

                using (cmd.Connection)
                {
                    cmd.Parameters.Add(new SqlParameter("@NAME", SqlDbType.VarChar, 1000));
                    cmd.Parameters[0].Value = sheetName;
                    cmd.Parameters.Add(new SqlParameter("@PARAMETERID", SqlDbType.UniqueIdentifier));
                    cmd.Parameters[1].Value = g;
                    cmd.Connection.Open();

                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception)
            {
                
            }
            
        }

        private void releaseObject(object obj)
        {
            string szError = string.Empty;

            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                szError = "Exception Occured while releasing object " + ex.Message;
            }
            finally
            {
                GC.Collect();
            }
        }


        public string[] GenerateSTEMAILRep(string szinfo)
        {
            return GenerateSTEMAILReport(szinfo.Split('|'));
        }
        public string[] GenerateSTEMAILReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];

            string sznetcode = string.Empty;
            string szvaldate = string.Empty;
            string szReference = string.Empty;
            string szEndDate = string.Empty;

            string szValdate = string.Empty;
            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;
            string szError1 = string.Empty;
            string szError2 = string.Empty;
            string szError3 = string.Empty;
            string szError4 = string.Empty;
            string szError5 = string.Empty;
            string szError6 = string.Empty;
            string szError7 = string.Empty;
            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;
            DataSet dsSubOut1 = null;
            DataSet dsSubOut2 = null;
            DataSet dsSubOut3 = null;
            DataSet dsSubOut4 = null;
            DataSet dsSubOut5 = null;
            DataSet dsSubOut6 = null;
            DataSet dsSubOut7 = null;
            string[] szReadInfo = new string[11];
            string[] szInfoIn = new string[2];
            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szEndDate = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szAppPath = InfoIn[1];
                }


                if ((InfoIn.Length - 1) > 1)
                {
                    szValdate = InfoIn[2];
                }

                if ((InfoIn.Length - 1) > 2)
                {
                    szReference = InfoIn[3];
                }

                if ((InfoIn.Length - 1) > 3)
                {
                    szInfoIn[0] = InfoIn[4];
                }
                if ((InfoIn.Length - 1) > 4)
                {
                    szInfoIn[1] = InfoIn[5];
                }
                string szDoc = string.Empty;



                //szReadInfo[0] = "HKCHN";
                //szReadInfo[0] = "GBLON";
                //szEndDate = "04/08/2009";
                //szValdate = "04/15/2009";
                //szReference = "March";

                DataSet dsEmail = null;


                try
                {




                    szReadInfo[0] = szInfoIn[0];
                    szReadInfo[1] = szEndDate;
                    szReadInfo[2] = szValdate;
                    szReadInfo[3] = szReference;



                    szError = GetDataAsCursorZAJNBRpt1(szConnStr, "spReport1", szReadInfo, ref dsMainOut);
                    if (szError == string.Empty)
                    {
                        szError1 = GetDataAsCursorZAJNBRpt1(szConnStr, "spreport2", szReadInfo, ref dsSubOut6);
                        szError2 = GetDataAsCursorZAJNBRpt(szConnStr, "spfxsoffer", szReadInfo, ref dsSubOut1);
                        szError3 = GetDataAsCursorZAJNBRpt(szConnStr, "spReceivables", szReadInfo, ref dsSubOut2);
                        szError4 = GetDataAsCursorZAJNBRpt(szConnStr, "spPayables", szReadInfo, ref dsSubOut3);
                        szError5 = GetDataAsCursorZAJNBRpt(szConnStr, "sptotals", szReadInfo, ref dsSubOut4);
                        szError6 = GetDataAsCursorZAJNBRpt(szConnStr, "spCurrexchange", szReadInfo, ref dsSubOut5);
                        szError7 = GetDataAsCursorZAJNBRpt(szConnStr, "spRecPayPositionCurr", szReadInfo, ref dsSubOut7);

                        if (szError1 == string.Empty && szError2 == string.Empty && szError3 == string.Empty)
                        {
                            rpt = new ReportDocument();
                            strFilename = szReadInfo[0] + System.DateTime.Now.Millisecond + System.DateTime.Now;
                            strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                            strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                            strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");

                            //  string szPath = GetWSPath();
                            report_name = _ReportLocation + "STNEW.rpt";
                            rpt.Load(report_name);
                            rpt.OpenSubreport("Mainsub.rpt");
                            rpt.OpenSubreport("fxoffer.rpt");
                            rpt.OpenSubreport("receivable.rpt");
                            rpt.OpenSubreport("payable.rpt");
                            rpt.OpenSubreport("Total.rpt");
                            rpt.OpenSubreport("ratestable.rpt");
                            rpt.OpenSubreport("PoscurrStation.rpt");
                            rpt.Refresh();

                            if (dsMainOut.Tables.Count == 0)
                            {
                                szError = "No Records Found";
                            }
                            else
                            {
                                rpt.SetDataSource(dsMainOut.Tables[0]);
                                rpt.Subreports["Mainsub.rpt"].SetDataSource(dsSubOut6.Tables[0]);
                                rpt.Subreports["fxoffer.rpt"].SetDataSource(dsSubOut1.Tables[0]);
                                rpt.Subreports["receivable.rpt"].SetDataSource(dsSubOut2.Tables[0]);
                                rpt.Subreports["payable.rpt"].SetDataSource(dsSubOut3.Tables[0]);
                                rpt.Subreports["Total.rpt"].SetDataSource(dsSubOut4.Tables[0]);
                                rpt.Subreports["ratestable.rpt"].SetDataSource(dsSubOut5.Tables[0]);
                                rpt.Subreports["PoscurrStation.rpt"].SetDataSource(dsSubOut7.Tables[0]);
                                rpt.ReportOptions.EnableSaveDataWithReport = true;
                                rpt.Refresh();




                                CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                                rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                                rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                                strFilename = strFilename + ".pdf";
                                //rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.Excel;
                                //strFilename = strFilename + ".xls";
                                //myexportfile = szAppPath + "docs/" + strFilename;
                                myexportfile = _DocPath + strFilename;

                                DiskOpts.DiskFileName = myexportfile;
                                szReportPathName = myexportfile;
                                rpt.ExportOptions.DestinationOptions = DiskOpts;
                                rpt.Export();
                                rpt.Close();
                                rpt.Dispose();
                                rpt = null;
                                szDoc = szReportPathName;
                                myexportfile = _DocumentURL + strFilename;

                            }
                        }
                    }
                }






                catch (Exception ex)
                {

                    throw;
                }
            }

            catch (Exception ex)
            {
                szError = "(Generate_STEMAIL) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }
                if (!(dsSubOut1 == null))
                {
                    dsSubOut1.Dispose();
                }
                if (!(dsSubOut2 == null))
                {
                    dsSubOut2.Dispose();
                }
                if (!(dsSubOut3 == null))
                {
                    dsSubOut3.Dispose();
                }
            }
            return szReturn_Array;
        }

        private bool GetEmailData(ref DataSet dsOutOnly, string szConnStr)
        {
            string strquery = "SELECT NettingCode, isnull(Email,'N')  from NettingCorps WHERE     (NettingCode NOT IN (SELECT DISTINCT ConsolidateCode FROM  NettingCorps AS NettingCorps_1  WHERE      (ConsolidateCode IS NOT NULL))) ORDER BY NettingCode ";
            string[] szDataIn = new string[11];
            string szError = null;
            bool bSuccessful = true;

            try
            {
                szError = GetDataSet(szConnStr, strquery, ref dsOutOnly);

                if (szError != string.Empty)
                {

                    bSuccessful = false;
                }
            }

            catch (Exception ex)
            {
                bSuccessful = false;

            }

            return bSuccessful;
        }
        public string GetDataSet(string szConnStr, string query, ref DataSet dsOut)
        {
            string szError = string.Empty;


            try
            {
                DataSet ds = null;
                SqlDataAdapter da;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();

                SqlCommand cmd = new SqlCommand(query, cn);
                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);

                dsOut = ds;


                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }


        public string GenerateJasRpt(string InfoPass)
        {
            char[] splitChars = new char[] { '|' };
            string[] InfoIn = InfoPass.Split(splitChars);
            string szgnstr;
            string[] szReturn_Array = new string[5];
            string szRptName = string.Empty;
            string[] szdata = new string[2];
            string szReportType = string.Empty;
            string szUserLoginId = string.Empty;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szErrUpdOrcPrnData = string.Empty;
            string szError = string.Empty;
            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string strFilename = string.Empty;
            string szReportPathName = string.Empty;
            DataSet dsMainOut = null;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            DataSet dsSubOut1 = null;
            string szError1 = string.Empty;
            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szgnstr = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szdata[0] = InfoIn[1];
                }
                if ((InfoIn.Length - 1) > 1)
                {
                    szdata[1] = InfoIn[2];
                }

                if ((InfoIn.Length - 1) > 2)
                {
                    szReportType = InfoIn[3];
                }

                string szProcName;
                szProcName = "spWarehouseReport";
                string[] szReadInfo = new string[11];
                szReadInfo[0] = szReportType;
                szReadInfo[1] = szdata[0];
                szReadInfo[2] = szdata[1];
                szError = GetDataAsCursor(szConnStr, szProcName, szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {
                    if (szReadInfo[0] == "WhseReceipt")
                    {
                        szError1 = GetDataAsCursor(szConnStr, "spSumRecpt", szReadInfo, ref dsSubOut1);
                        szRptName = "WhseReceipt.rpt";
                    }
                    else if (szReadInfo[0] == "WhsePackingList")
                    {
                        szRptName = "WhsePackingList.rpt";
                    }
                    else if (szReadInfo[0] == "WhsePickList")
                    {
                        szRptName = "WhsePickList.rpt";
                    }
                    else if (szReadInfo[0] == "WhseShippingOrder")
                    {
                        szRptName = "WhseShippingOrder.rpt";
                    }
                    else if (szReadInfo[0] == "WhseShippingGatesOrder")
                    {
                        szRptName = "GatesCommercialInvoice.rpt";
                    }
                    rpt = new ReportDocument();
                    strFilename = "JASOrbis" + szReportType + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");
                    report_name = _ReportLocation + szRptName;
                    rpt.Load(report_name);
                    if (szReadInfo[0] == "WhseReceipt")
                    {
                        rpt.OpenSubreport("sumrcpt.rpt");
                    }
                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);
                        if (szReadInfo[0] == "WhseReceipt")
                        {
                            rpt.Subreports["sumrcpt.rpt"].SetDataSource(dsSubOut1.Tables[0]);

                        }

                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();

                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                        rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                        strFilename = strFilename + ".pdf";

                        myexportfile = _DocPath + strFilename;
                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        //myexportfile = _DocumentURL + strFilename;
                    }

                }
            }
            catch (Exception ex)
            {
                szError = "(Jas Orbis Report) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = _DocumentURL + strFilename;
                szReturn_Array[2] = myexportfile;
            }
            return string.Join("|", szReturn_Array);
        }



        public string GenerateGLSRpt(ref string[] szInfoIn)
        {

            string InfoPass = szInfoIn[14];
            string[] szReturn_Array = new string[5];
            string szRptName = string.Empty;
            string[] szdata = new string[2];
            string szReportType = string.Empty;
            string szUserLoginId = string.Empty;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szErrUpdOrcPrnData = string.Empty;
            string szError = string.Empty;
            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string strFilename = string.Empty;
            string szReportPathName = string.Empty;
            DataSet dsMainOut = null;
            string szConnStr = Properties.Settings.Default.ConnectionString;

            string szError1 = string.Empty;
            try
            {

                string szProcName;
                szProcName = "spGLSBooking_Report";
                string[] szReadInfo = new string[11];
                szReadInfo[0] = InfoPass;
                szError = GetDataAsCursorGLS(szConnStr, szProcName, szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {

                    szRptName = "GLSBooking.rpt";

                    rpt = new ReportDocument();
                    strFilename = "GLSBooking" + szReportType + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");
                    report_name = _ReportLocation + szRptName;
                    rpt.Load(report_name);

                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);


                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();

                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                        rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                        strFilename = strFilename + ".pdf";

                        szInfoIn[2] = dsMainOut.Tables[0].Rows[0]["EmailTo"].ToString();
                        szInfoIn[4] = szInfoIn[4] + dsMainOut.Tables[0].Rows[0]["BookingNo"].ToString();

                        myexportfile = _DocPath + strFilename;
                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                    }

                }
            }
            catch (Exception ex)
            {
                szError = "(Jas GLS Report) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = _DocumentURL + strFilename;
                szReturn_Array[2] = myexportfile;
            }
            return string.Join("|", szReturn_Array);
        }




        public string GeneratelcsdRpt(string InfoPass)
        {


            string[] szReturn_Array = new string[5];
            string szRptName = string.Empty;
            string[] szdata = new string[2];
            string szReportType = string.Empty;
            string szUserLoginId = string.Empty;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szErrUpdOrcPrnData = string.Empty;
            string szError = string.Empty;
            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string strFilename = string.Empty;
            string szReportPathName = string.Empty;
            DataSet dsMainOut = null;
            string szConnStr = Properties.Settings.Default.ConnectionString;

            string szError1 = string.Empty;
            try
            {



                string szProcName;
                szProcName = "spReportLCSD";
                string[] szReadInfo = new string[11];
                szReadInfo[0] = InfoPass;
                szError = GetDataAsCursorlcsd(szConnStr, szProcName, szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {

                    szRptName = "JasLCSDRep.rpt";

                    rpt = new ReportDocument();
                    strFilename = "JASlcsd" + szReportType + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");
                    report_name = _ReportLocation + szRptName;
                    rpt.Load(report_name);

                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);


                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();

                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                        //rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                        //strFilename = strFilename + ".pdf";
                        rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.Excel;
                        strFilename = strFilename + ".xls";

                        myexportfile = _DocPath + strFilename;
                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        //myexportfile = _DocumentURL + strFilename;
                    }

                }
            }
            catch (Exception ex)
            {
                szError = "(Jas lcsd Report) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = _DocumentURL + strFilename;
                szReturn_Array[2] = myexportfile;
            }
            return string.Join("|", szReturn_Array);
        }

        #region CommentedOut

        //
        //public string GeneratelcsdDetRpt(string InfoPass)
        //{


        //    string[] szReturn_Array = new string[5];
        //    string szRptName = string.Empty;
        //    string[] szdata = new string[2];
        //    string szReportType = string.Empty;
        //    string szUserLoginId = string.Empty;
        //    string szAppPath = string.Empty;
        //    string myexportfile = string.Empty;
        //    string szErrUpdOrcPrnData = string.Empty;
        //    string szError = string.Empty;
        //    string report_name = string.Empty;
        //    ReportDocument rpt = new ReportDocument();
        //    string strFilename = string.Empty;
        //    string szReportPathName = string.Empty;
        //    DataSet dsMainOut = null;
        //    string szConnStr = Properties.Settings.Default.ConnectionString;

        //    string szError1 = string.Empty;
        //    try
        //    {



        //        string szProcName;
        //        szProcName = "spReportLCSDDET";
        //        string[] szReadInfo = new string[11];
        //        szReadInfo[0] = InfoPass;
        //        szError = GetDataAsCursorlcsd(szConnStr, szProcName, szReadInfo, ref dsMainOut);
        //        if (szError == string.Empty)
        //        {

        //            szRptName = "JasLCSDDetRep.rpt";

        //            rpt = new ReportDocument();
        //            strFilename = "JASlcsdDet" + szReportType + System.DateTime.Now.Millisecond + System.DateTime.Now;
        //            strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
        //            strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
        //            strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");
        //            report_name = _ReportLocation + szRptName;
        //            rpt.Load(report_name);

        //            rpt.Refresh();

        //            if (dsMainOut.Tables[0].Rows.Count == 0)
        //            {
        //                szError = "No Records Found";
        //            }
        //            else
        //            {
        //                rpt.SetDataSource(dsMainOut.Tables[0]);


        //                rpt.ReportOptions.EnableSaveDataWithReport = true;
        //                rpt.Refresh();

        //                CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
        //                rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
        //                //rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
        //                //strFilename = strFilename + ".pdf";
        //                rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.Excel;
        //                strFilename = strFilename + ".xls";

        //                myexportfile = _DocPath + strFilename;
        //                DiskOpts.DiskFileName = myexportfile;
        //                szReportPathName = myexportfile;
        //                rpt.ExportOptions.DestinationOptions = DiskOpts;
        //                rpt.Export();
        //                rpt.Close();
        //                rpt.Dispose();
        //                rpt = null;
        //                //myexportfile = _DocumentURL + strFilename;
        //            }

        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        szError = "(Jas lcsd  Detail Report) " + ex.Message;
        //    }
        //    finally
        //    {
        //        szReturn_Array[0] = szError;
        //        szReturn_Array[1] = _DocumentURL + strFilename;
        //        szReturn_Array[2] = myexportfile;
        //    }
        //    return string.Join("|", szReturn_Array);
        //}

        //Modified By Kavita
        //Dated Aug-13-2010  

        //Commented out by kavita date 09/20/2011
        //Puma is advising they are receiving these reports multiple times within the same day. This Sub get replaced by GeneratelcsdDetRpt(string sAcctId, string sType, int sFileId)

        //public string GeneratelcsdDetRpt(string InfoPass)
        //{


        //    string[] szReturn_Array = new string[5];
        //    string szRptName = string.Empty;
        //    string[] szdata = new string[2];
        //    string szReportType = string.Empty;
        //    string szUserLoginId = string.Empty;
        //    string szAppPath = string.Empty;
        //    string myexportfile = string.Empty;
        //    string szErrUpdOrcPrnData = string.Empty;
        //    string szError = string.Empty;
        //    string report_name = string.Empty;
        //    string strFilename = string.Empty;
        //    string szReportPathName = string.Empty;
        //    DataSet dsMainOut = null;
        //    string szConnStr = Properties.Settings.Default.ConnectionString;

        //    string szError1 = string.Empty;

        //    int rowNo = 7;

        //    Excel.Application xlApp;
        //    Excel.Workbook xlWorkBook;
        //    Excel.Worksheet xlWorkSheet;
        //    Excel.Range workSheet_range = null;
        //    object misValue = System.Reflection.Missing.Value;

        //    xlApp = new Excel.ApplicationClass();
        //    xlWorkBook = xlApp.Workbooks.Add(misValue);
        //    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        //    try
        //    {

        //        string szProcName;
        //        szProcName = "spReportLCSDDET";
        //        string[] szReadInfo = new string[11];
        //        szReadInfo[0] = InfoPass;
        //        szError = GetDataAsCursorlcsd(szConnStr, szProcName, szReadInfo, ref dsMainOut);
        //        if (szError == string.Empty)
        //        {

        //            strFilename = "JASlcsdDet" + szReportType + System.DateTime.Now.Millisecond + System.DateTime.Now;
        //            strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
        //            strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
        //            strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");
        //            report_name = _ReportLocation + szRptName;

        //            if (dsMainOut.Tables[0].Rows.Count == 0)
        //            {
        //                szError = "No Records Found";
        //            }
        //            else
        //            {
        //                //Add Image

        //                string _stPicture = "D:\\etrack_net\\JasOrbisSL\\wsJas\\jasorbis1.png";
        //                xlWorkSheet.Shapes.AddPicture(_stPicture, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 200, 10, 150, 50);

        //                //Add Report Title

        //                xlWorkSheet.Cells[6, 3] = "LCSD Report (Detail)";
        //                workSheet_range = (Excel.Range)xlWorkSheet.Cells[6, 3];
        //                workSheet_range.Font.Bold = true;

        //                workSheet_range = xlApp.ActiveCell;
        //                workSheet_range.EntireRow.WrapText = false;

        //                //Add Report Columns

        //                for (int columnNo = 1; columnNo <= 9; columnNo++)
        //                {
        //                    switch (columnNo)
        //                    {
        //                        case 1:
        //                            xlWorkSheet.Cells[7, columnNo] = "Supplier Code";
        //                            break;
        //                        case 2:
        //                            xlWorkSheet.Cells[7, columnNo] = "Supplier Name";
        //                            break;
        //                        case 3:
        //                            xlWorkSheet.Cells[7, columnNo] = "Factory Name";
        //                            break;
        //                        case 4:
        //                            xlWorkSheet.Cells[7, columnNo] = "Customer";
        //                            break;
        //                        case 5:
        //                            xlWorkSheet.Cells[7, columnNo] = "LSCD Date";
        //                            break;
        //                        case 6:
        //                            xlWorkSheet.Cells[7, columnNo] = "RSD";
        //                            break;
        //                        case 7:
        //                            xlWorkSheet.Cells[7, columnNo] = "Order #";
        //                            break;
        //                        case 8:
        //                            xlWorkSheet.Cells[7, columnNo] = "Article";
        //                            break;
        //                        case 9:
        //                            xlWorkSheet.Cells[7, columnNo] = "Ship Mode";
        //                            break;

        //                    }

        //                    workSheet_range = (Excel.Range)xlWorkSheet.Cells[7, columnNo];
        //                    workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb();
        //                    workSheet_range.Font.Bold = true;
        //                    workSheet_range.ColumnWidth = 15;

        //                }

        //                //Add Row Data

        //                foreach (DataRow row in dsMainOut.Tables[0].Rows)
        //                {
        //                    rowNo++;
        //                    for (int i = 0; i <= dsMainOut.Tables[0].Columns.Count - 1; i++)
        //                    {

        //                        xlWorkSheet.Cells[rowNo, i + 1] = row[i].ToString();
        //                        workSheet_range = (Excel.Range)xlWorkSheet.Cells[rowNo, i + 1];
        //                        workSheet_range.WrapText = true;
        //                        workSheet_range.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
        //                    }

        //                }

        //                strFilename = strFilename + ".xls";

        //                myexportfile = _DocPath + strFilename;

        //                xlWorkBook.SaveAs(myexportfile, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        //                xlWorkBook.Close(true, misValue, misValue);

        //            }

        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        szError = "(Jas lcsd  Detail Report) " + ex.Message;
        //    }
        //    finally
        //    {
        //        szReturn_Array[0] = szError;
        //        szReturn_Array[1] = _DocumentURL + strFilename;
        //        szReturn_Array[2] = myexportfile;

        //        xlApp.Quit();

        //        releaseObject(xlWorkSheet);
        //        releaseObject(xlWorkBook);
        //        releaseObject(xlApp);

        //        if (!(dsMainOut == null))
        //        {
        //            dsMainOut.Dispose();
        //        }

        //    }
        //    return string.Join("|", szReturn_Array);
        //}


        //Added by kavita date 09/19/2011
        //Puma is advising they are receiving these reports multiple times within the same day Ref No:17356 Title: LCSD Report by Email 

        #endregion

        public string[] GeneratelcsdDetRpt(string sAcctId, string sType, int sFileId)
        {


            string[] szReturn_Array = new string[5];
            string myexportfile = string.Empty;
            string szError = string.Empty;
            string strFilename = string.Empty;
            string szConnStr = Properties.Settings.Default.ConStrJasFilesDB;

            int rowNo = 7;

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range workSheet_range = null;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            SqlConnection Conn = new SqlConnection(szConnStr);
            DataSet dsData = new DataSet();
            string strData = string.Empty;




            try
            {
                SqlCommand command = new SqlCommand("uspFILOUTGetFiles", Conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Acct", sAcctId);
                command.Parameters.AddWithValue("@Type", sType);
                command.Parameters.AddWithValue("@Fileid", sFileId);


                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dsData);


                if (dsData.Tables.Count > 0)
                {

                    if (dsData.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {

                        strData = dsData.Tables[0].Rows[0]["FileData"].ToString();
                        strFilename = dsData.Tables[0].Rows[0]["FileName"].ToString();
                        string[] lines = strData.Split(new string[] { "\r\n" }, StringSplitOptions.None);

                        if (lines.Length > 0)
                        {

                            //Create Spread sheet 

                            //Add Image

                            string _stPicture = Properties.Settings.Default.ImagePath; //"D:\\etrack_net\\JasOrbisSL\\wsJas\\jasorbis1.png"; // need to work kavita

                            xlWorkSheet.Shapes.AddPicture(_stPicture, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 200, 10, 150, 50);

                            //Add Report Title

                            xlWorkSheet.Cells[6, 3] = "LCSD Report (Detail)";
                            workSheet_range = (Excel.Range)xlWorkSheet.Cells[6, 3];
                            workSheet_range.Font.Bold = true;

                            workSheet_range = xlApp.ActiveCell;
                            workSheet_range.EntireRow.WrapText = false;

                            //Add Report Columns

                            for (int columnNo = 1; columnNo <= 9; columnNo++)
                            {
                                switch (columnNo)
                                {
                                    case 1:
                                        xlWorkSheet.Cells[7, columnNo] = "Supplier Code";
                                        break;
                                    case 2:
                                        xlWorkSheet.Cells[7, columnNo] = "Supplier Name";
                                        break;
                                    case 3:
                                        xlWorkSheet.Cells[7, columnNo] = "Factory Name";
                                        break;
                                    case 4:
                                        xlWorkSheet.Cells[7, columnNo] = "Customer";
                                        break;
                                    case 5:
                                        xlWorkSheet.Cells[7, columnNo] = "LSCD Date";
                                        break;
                                    case 6:
                                        xlWorkSheet.Cells[7, columnNo] = "RSD";
                                        break;
                                    case 7:
                                        xlWorkSheet.Cells[7, columnNo] = "Order #";
                                        break;
                                    case 8:
                                        xlWorkSheet.Cells[7, columnNo] = "Article";
                                        break;
                                    case 9:
                                        xlWorkSheet.Cells[7, columnNo] = "Ship Mode";
                                        break;

                                }
                                workSheet_range = (Excel.Range)xlWorkSheet.Cells[7, columnNo];
                                workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb();
                                workSheet_range.Font.Bold = true;
                                workSheet_range.ColumnWidth = 15;

                            }

                            //Add Row Data

                            foreach (string row in lines)
                            {
                                rowNo++;

                                string[] strColData = row.ToString().Split('|');

                                if (strColData.Length > 1)
                                {
                                    for (int i = 0; i <= 8; i++)
                                    {
                                        xlWorkSheet.Cells[rowNo, i + 1] = strColData[i].ToString().Replace("\"", string.Empty);
                                        workSheet_range = (Excel.Range)xlWorkSheet.Cells[rowNo, i + 1];
                                        workSheet_range.WrapText = true;
                                        workSheet_range.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
                                    }
                                }
                            }

                            myexportfile = _DocPath + strFilename;

                            //Kavita 07/09/2012 . In order to surpress the prompt window
                            xlApp.Visible = false;
                            xlApp.DisplayAlerts = false;
                            //End 07/09/2012

                            xlWorkBook.SaveAs(myexportfile, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                            xlWorkBook.Saved = true;
                            xlWorkBook.Close(true, misValue, misValue);


                        }



                    }
                }

            }


            catch (Exception ex)
            {
                szError = "(Jas lcsd  Detail Report) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = "";
                szReturn_Array[2] = myexportfile;

                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                if (!(dsData == null))
                {
                    dsData.Dispose();
                }

                Conn.Close();
            }
            return szReturn_Array;
        }



        //Added by kavita date: 10/22/2012
        //BNC (Brazilian Naval Commision) PO OnHand report 

        public string[] GenerateBNCPOOnHandRpt(string sAcctId, string sType, int sFileId)
        {

            string[] szReturn_Array = new string[5];
            string myexportfile = string.Empty;
            string szError = string.Empty;
            string strFilename = string.Empty;
            string szConnStr = Properties.Settings.Default.ConStrJasFilesDB;

            int rowNo = 10;

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range workSheet_range = null;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            SqlConnection Conn = new SqlConnection(szConnStr);
            DataSet dsData = new DataSet();
            string strData = string.Empty;


            try
            {
                SqlCommand command = new SqlCommand("uspFILOUTGetFiles", Conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Acct", sAcctId);
                command.Parameters.AddWithValue("@Type", sType);
                command.Parameters.AddWithValue("@Fileid", sFileId);


                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dsData);


                if (dsData.Tables.Count > 0)
                {

                    if (dsData.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {

                        strData = dsData.Tables[0].Rows[0]["FileData"].ToString();
                        strFilename = dsData.Tables[0].Rows[0]["FileName"].ToString();
                        string[] lines = strData.Split(new string[] { "\r\n" }, StringSplitOptions.None);

                        if (lines.Length > 0)
                        {

                            //Create Spread sheet 
                            //Add Image

                            string _stPicture = Properties.Settings.Default.ImagePath;
                            xlWorkSheet.Shapes.AddPicture(_stPicture, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 200, 10, 150, 50);

                            //Add Report Title
                            xlWorkSheet.Cells[6, 3] = "On Hand Report";
                            workSheet_range = (Excel.Range)xlWorkSheet.Cells[6, 3];
                            workSheet_range.Font.Bold = true;


                            xlWorkSheet.Cells[7, 3] = "Customer BNC - Brazilian Naval Commission         Date:" + System.DateTime.Today.ToString("d");
                            workSheet_range = (Excel.Range)xlWorkSheet.Cells[6, 3];
                            workSheet_range.Font.Bold = true;

                            workSheet_range = xlApp.ActiveCell;
                            workSheet_range.EntireRow.WrapText = false;

                            //Add Report Columns
                            string[] Cols = lines[0].ToString().Split(new string[] { "," }, StringSplitOptions.None);
                            int columnNo = 1;
                            foreach (string col in Cols)
                            {

                                xlWorkSheet.Cells[9, columnNo] = col.ToString();
                                workSheet_range = (Excel.Range)xlWorkSheet.Cells[9, columnNo];
                                workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb();
                                workSheet_range.Font.Bold = true;
                                if (columnNo == 2 || columnNo == 7 || columnNo == 8 || columnNo == 10)
                                {
                                    workSheet_range.ColumnWidth = 30;
                                }
                                else if (columnNo == 11)
                                {
                                    workSheet_range.ColumnWidth = 40;

                                }
                                else
                                {
                                    workSheet_range.ColumnWidth = 15;
                                }
                                columnNo = columnNo + 1;
                            }


                            //Add Row Data
                            ///Skipping first row as it contains column name

                            foreach (string row in lines.Skip(1))
                            {


                                string[] strColData = row.ToString().Split(',');

                                if (strColData.Length > 1)
                                {
                                    for (int i = 0; i <= Cols.Length - 1; i++)
                                    {
                                        workSheet_range = (Excel.Range)xlWorkSheet.Cells[rowNo, i + 1];
                                        workSheet_range.NumberFormat = "@";
                                        xlWorkSheet.Cells[rowNo, i + 1] = strColData[i].ToString().Replace("\"", string.Empty);
                                        workSheet_range.WrapText = true;
                                        workSheet_range.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
                                    }
                                }

                                rowNo++;
                            }

                            myexportfile = _DocPath + strFilename;

                            //In order to surpress the prompt window
                            xlApp.Visible = false;
                            xlApp.DisplayAlerts = false;
                            //End

                            xlWorkBook.SaveAs(myexportfile, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                            xlWorkBook.Saved = true;
                            xlWorkBook.Close(true, misValue, misValue);


                        }



                    }
                }

            }


            catch (Exception ex)
            {
                szError = "(Jas BNCPOOnHand Report) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = "";
                szReturn_Array[2] = myexportfile;

                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                if (!(dsData == null))
                {
                    dsData.Dispose();
                }

                Conn.Close();
            }
            return szReturn_Array;
        }



        public string GenerateTrnReport(string InfoPass)
        {
            char[] splitChars = new char[] { '|' };
            string[] InfoIn = InfoPass.Split(splitChars);

            string[] szReturn_Array = new string[6];

            string szAccount = string.Empty;
            string szPartNo = string.Empty;
            string szReferenceNo = string.Empty;
            string szCutoffDate = string.Empty;
            string szSummary = string.Empty;
            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;
            string szError1 = string.Empty;
            string szError2 = string.Empty;
            string szError3 = string.Empty;
            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;
            DataSet dsSubOut1 = null;
            DataSet dsSubOut2 = null;
            DataSet dsSubOut3 = null;

            try
            {

                if ((InfoIn.Length - 1) > -1)
                {
                    szAccount = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szPartNo = InfoIn[1];
                }


                if ((InfoIn.Length - 1) > 1)
                {
                    szReferenceNo = InfoIn[2];
                }

                if ((InfoIn.Length - 1) > 2)
                {
                    szCutoffDate = InfoIn[3];
                }

                if ((InfoIn.Length - 1) > 3)
                {
                    szSummary = InfoIn[4];
                }
                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];
                szReadInfo[0] = szAccount;
                szReadInfo[1] = szPartNo;
                szReadInfo[2] = szReferenceNo;
                szReadInfo[3] = szCutoffDate;
                szReadInfo[4] = szSummary;
                szError = GetDataAsCursorTrnRpt(szConnStr, "spTransSummary", szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {
                    szError1 = GetDataAsCursorTrnRpt(szConnStr, "spTransRecDetRept", szReadInfo, ref dsSubOut1);
                    szError2 = GetDataAsCursorTrnRpt(szConnStr, "spTransShipDetRept", szReadInfo, ref dsSubOut2);
                    szError3 = GetDataAsCursorTrnRpt(szConnStr, "spTransInvAdjRept", szReadInfo, ref dsSubOut3);


                    if (szError1 == string.Empty && szError2 == string.Empty && szError3 == string.Empty)
                    {
                        rpt = new ReportDocument();
                        strFilename = "InvTrans" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");

                        string szPath = GetWSPath();
                        report_name = _ReportLocation + "WHSE_TRANSACTIONS_RPT.rpt";
                        rpt.Load(report_name);
                        rpt.OpenSubreport("receipt.rpt");
                        rpt.OpenSubreport("shippingOrder.rpt");
                        rpt.OpenSubreport("InventAdjust.rpt");
                        rpt.Refresh();
                        if (dsMainOut.Tables[0].Rows.Count == 0)
                        {
                            szError = "No Records Found";
                        }
                        else
                        {
                            rpt.SetDataSource(dsMainOut.Tables[0]);

                            rpt.Subreports["receipt.rpt"].SetDataSource(dsSubOut1.Tables[0]);
                            rpt.Subreports["shippingOrder.rpt"].SetDataSource(dsSubOut2.Tables[0]);
                            rpt.Subreports["InventAdjust.rpt"].SetDataSource(dsSubOut3.Tables[0]);
                            rpt.ReportOptions.EnableSaveDataWithReport = true;
                            rpt.Refresh();

                            CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                            rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                            rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                            strFilename = strFilename + ".pdf";
                            myexportfile = _DocPath + strFilename;
                            DiskOpts.DiskFileName = myexportfile;
                            szReportPathName = myexportfile;
                            rpt.ExportOptions.DestinationOptions = DiskOpts;
                            rpt.Export();
                            rpt.Close();
                            rpt.Dispose();
                            rpt = null;
                            myexportfile = _DocumentURL + strFilename;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(WH Generate_InvTrans) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }
                if (!(dsSubOut1 == null))
                {
                    dsSubOut1.Dispose();
                }
                if (!(dsSubOut2 == null))
                {
                    dsSubOut2.Dispose();
                }
                if (!(dsSubOut3 == null))
                {
                    dsSubOut3.Dispose();
                }
            }
            return string.Join("|", szReturn_Array);
        }

        public string GenBOLRep(string szinfo)
        {
            string[] strArr;
            strArr = GenBOLReport(szinfo.Split('|'));
            string str = string.Join("|", strArr);
            return str;
        }

        public string[] GenBOLReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];


            string szLoginID = String.Empty;
            string szKey = String.Empty;
            string szType = String.Empty;

            string szReportPathName = string.Empty;
           string szConnStr = Properties.Settings.Default.ConnectionString;

            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;


            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szAppPath = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szLoginID = InfoIn[1];
                }

                if ((InfoIn.Length - 1) > 1)
                {
                    szKey = InfoIn[2];
                }

                if ((InfoIn.Length - 1) > 2)
                {
                    szType = InfoIn[3];
                }

                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];

                szReadInfo[0] = szLoginID;
                szReadInfo[1] = szKey;
                szReadInfo[2] = szType;

                szError = GetDataAsCursorOrbisRpt(szConnStr, "spRptBOL", szReadInfo, "BOL", ref dsMainOut);
                if (szError == string.Empty)
                {

                    rpt = new ReportDocument();
                    strFilename = "BOL" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");

                    string szPath = GetWSPath();
                    //report_name = szPath + "BOL.rpt";
                    report_name = _ReportLocation + "BOL.rpt";
                    rpt.Load(report_name);

                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);
                        rpt.Subreports["MarkNos.rpt"].SetDataSource(dsMainOut.Tables[1]);
                        rpt.Subreports["ContainerInfo.rpt"].SetDataSource(dsMainOut.Tables[2]);
                        rpt.Subreports["Charges.rpt"].SetDataSource(dsMainOut.Tables[3]);

                        //rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();

                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                        rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                        strFilename = strFilename + ".pdf";

                        // myexportfile = szPath + "docs/" + strFilename;
                        myexportfile = _DocPath + strFilename;

                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        myexportfile = _DocumentURL + strFilename;


                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_BOL) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }


        public string GenAWBRep(string szinfo)
        {
            string[] strArr;
            strArr = GenAWBReport(szinfo.Split('|'));
            string str = string.Join("|", strArr);
            return str;
        }

        public string[] GenAWBReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];


            string szLoginID = String.Empty;
            string szKey = String.Empty;
            string szType = String.Empty;

            string szReportPathName = string.Empty;
           string szConnStr = Properties.Settings.Default.ConnectionString;

            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;


            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szAppPath = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szLoginID = InfoIn[1];
                }

                if ((InfoIn.Length - 1) > 1)
                {
                    szKey = InfoIn[2];
                }

                if ((InfoIn.Length - 1) > 2)
                {
                    szType = InfoIn[3];
                }

                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];

                szReadInfo[0] = szLoginID;
                szReadInfo[1] = szKey;
                szReadInfo[2] = szType;

                szError = GetDataAsCursorOrbisRpt(szConnStr, "spRptAWB", szReadInfo, "AWB", ref dsMainOut);
                if (szError == string.Empty)
                {


                    rpt = new ReportDocument();
                    strFilename = "AWB" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");

                    string szPath = GetWSPath();
                    //report_name = szPath + "AWB.rpt";
                    report_name = _ReportLocation + "AWB.rpt";
                    rpt.Load(report_name);

                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);
                        rpt.Subreports["LineDetails.rpt"].SetDataSource(dsMainOut.Tables[1]);
                        rpt.Subreports["Charges.rpt"].SetDataSource(dsMainOut.Tables[2]);
                        //rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();

                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                        rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                        strFilename = strFilename + ".pdf";

                        //myexportfile = szAppPath + "docs/" + strFilename;
                        //myexportfile = szPath + "docs/" + strFilename;
                        myexportfile = _DocPath + strFilename;

                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        //myexportfile = "http://96.57.0.2:5432/JasWebService/reports/docs/" + strFilename;
                        myexportfile = _DocumentURL + strFilename;



                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_AWB) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        public string[] GenerateBooking(string szinfo)
        {
            return GenerateBookingRpt(szinfo.Split('|'));
        }

        public string[] GenerateBookingRpt(string[] InfoIn)
        {
            string szgnstr;
            string[] szReturn_Array = new string[5];
            string szRptName = string.Empty;
            string[] szdata = new string[2];
            string szReportType = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;

            string szUserLoginId = string.Empty;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szErrUpdOrcPrnData = string.Empty;
            string szError = string.Empty;
            string szError1 = string.Empty;
            string szError2 = string.Empty;
            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string strFilename = string.Empty;
            string szReportPathName = string.Empty;
            DataSet dsMainOut = null;
            DataSet dsSubOut1 = null;
            DataSet dsSubOut2 = null;
            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szgnstr = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szdata[0] = InfoIn[1];
                }


                if ((InfoIn.Length - 1) > 1)
                {
                    szReportType = InfoIn[2];
                }




                string szProcName;
                szProcName = "spBookingReport";
                string[] szReadInfo = new string[11];
                szReadInfo[0] = szdata[0];


                szError = GetDataAsCursorBooking(szConnStr, szProcName, szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {
                    szError1 = GetDataAsCursorBooking(szConnStr, "spBookingNotes", szReadInfo, ref dsSubOut1);
                    szError2 = GetDataAsCursorBooking(szConnStr, "spBookingPodtl", szReadInfo, ref dsSubOut2);
                    if (szError1 == string.Empty && szError2 == string.Empty)
                    {
                        rpt = new ReportDocument();
                        strFilename = "JasBooking" + szReportType + System.DateTime.Now.Millisecond + System.DateTime.Now;
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");




                        report_name = _ReportLocation + "JasBooking.rpt";
                        rpt.Load(report_name);
                        rpt.OpenSubreport("notes.rpt");
                        rpt.OpenSubreport("podtl.rpt");
                        rpt.Refresh();

                        if (dsMainOut.Tables[0].Rows.Count == 0)
                        {
                            szError = "No Records Found";
                        }
                        else
                        {



                            rpt.SetDataSource(dsMainOut.Tables[0]);
                            rpt.Subreports["notes.rpt"].SetDataSource(dsSubOut1.Tables[0]);
                            rpt.Subreports["podtl.rpt"].SetDataSource(dsSubOut2.Tables[0]);
                            rpt.ReportOptions.EnableSaveDataWithReport = true;
                            rpt.Refresh();

                            CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                            rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                            rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                            strFilename = strFilename + ".pdf";

                            myexportfile = _TempDocPath + strFilename;

                            ////Added by alfredo to save the temp booking file in the jasedm
                            //JASEDM.dbFileSave(myexportfile, "Email Program - Booking Routine", "Noref", "Automatically Generated", "Booking", "Email Program");

                            DiskOpts.DiskFileName = myexportfile;
                            szReportPathName = myexportfile;
                            rpt.ExportOptions.DestinationOptions = DiskOpts;
                            rpt.Export();
                            rpt.Close();
                            rpt.Dispose();
                            rpt = null;

                            //Added by alfredo to save the temp booking file in the jasedm
                            //JASEDM.dbFileSave(myexportfile, "Email Program Booking Routine", "Noref", "Automatically Generated", "Booking Temp", "Email Program");
                            //myexportfile = _TempDocumentURL + strFilename;
                            //retrieving the temp booking file from the jas edm
                            myexportfile = "http://kimamo.com/receiver.ashx?filename=" + strFilename;

                        }

                    }
                }
            }

            catch (Exception ex)
            {
                szError = "(Jas Booking Report) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }
                if (!(dsSubOut1 == null))
                {
                    dsSubOut1.Dispose();
                }
                if (!(dsSubOut2 == null))
                {
                    dsSubOut2.Dispose();
                }
            }
            return szReturn_Array;
        }

        public string[] GenBikeEx850Report(DataTable dsMainOut)
        {
            string[] szReturn_Array = new string[6];


            string szBegdate = string.Empty;
            string szEndDate = string.Empty;

            string szReportPathName = string.Empty;

            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;


            Excel.Application oExcel = new Excel.Application();
            Excel.Workbooks oBooks;
            Excel.Workbook oBook;
            Excel.Sheets oSheets;
            Excel.Worksheet oSheet;
            Excel.Range oCells;
            object misValue = System.Reflection.Missing.Value;



            try
            {

                string szDoc = string.Empty;

                if (szError == string.Empty)
                {


                    strFilename = "EX_BIKE_850" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");

                    report_name = _ReportLocation + "Bike850Ex.xls"; //@"D:\etrack_net\JasOrbisSL\wsJas\Reports\Bike850Ex.xls";

                    if (dsMainOut.Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {

                        oExcel.Visible = false;
                        oExcel.DisplayAlerts = false;

                        //Start a new workbook

                        oBooks = oExcel.Workbooks;
                        oBook = oBooks.Open(report_name, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
                        oSheets = oBook.Worksheets;
                        oSheet = (Excel.Worksheet)oSheets.get_Item(1);
                        oCells = oSheet.Cells;
                        DumpDataFORBikeEX(dsMainOut, oCells); //Fill in the data

                        strFilename = strFilename + ".xls";
                        myexportfile = _DocPath + strFilename;

                        oSheet.SaveAs(myexportfile, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); //Save in a temporary file
                        oBook.Saved = true;
                        oBook.Close(true, misValue, misValue);

                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_BikeRep) " + ex.Message;
            }
            finally
            {

                //Quit Excel and thoroughly deallocate everything
                oExcel.Quit();
                //releaseObject(oSheet);
                //releaseObject(oBook);
                releaseObject(oExcel);

                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        public string[] GenerateBookingNot(string szinfo)
        {
            return GenerateBookingNOTRpt(szinfo.Split('|'));
        }

        public string[] GenerateBookingNOTRpt(string[] InfoIn)
        {
            string szgnstr;
            string[] szReturn_Array = new string[5];
            string szRptName = string.Empty;
            string[] szdata = new string[2];
            string szReportType = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szUserLoginId = string.Empty;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szErrUpdOrcPrnData = string.Empty;
            string szError = string.Empty;
            string szError1 = string.Empty;
            string szError2 = string.Empty;
            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string strFilename = string.Empty;
            string szReportPathName = string.Empty;
            DataSet dsMainOut = null;

            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szgnstr = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szdata[0] = InfoIn[1];
                }


                if ((InfoIn.Length - 1) > 1)
                {
                    szReportType = InfoIn[2];
                }




                string szProcName;
                szProcName = "spBookingNOTReport";
                string[] szReadInfo = new string[11];
                szReadInfo[0] = szdata[0];


                szError = GetDataAsCursorBooking(szConnStr, szProcName, szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {

                    rpt = new ReportDocument();
                    strFilename = "JasBookingNotify" + szReportType + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");




                    report_name = _ReportLocation + "JasBookingNotification.rpt";
                    rpt.Load(report_name);

                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);

                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();

                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                        rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                        strFilename = strFilename + ".pdf";

                        myexportfile = _DocPath + strFilename;
                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        myexportfile = _DocumentURL + strFilename;
                    }


                }
            }

            catch (Exception ex)
            {
                szError = "(Jas Booking Notification Report) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }
        public string[] GenerateStationNewRep(string szinfo)
        {
            return GenerateStationNewReport(szinfo.Split('|'));
        }

        public string[] GenerateStationNewReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];

            string sznetcode = string.Empty;
            string szvaldate = string.Empty;
            string szReference = string.Empty;
            string szEndDate = string.Empty;

            string szValdate = string.Empty;
            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;
            string szError1 = string.Empty;
            string szError2 = string.Empty;
            string szError3 = string.Empty;
            string szError4 = string.Empty;
            string szError5 = string.Empty;
            string szError6 = string.Empty;
            string szError7 = string.Empty;
            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;
            DataSet dsSubOut1 = null;
            DataSet dsSubOut2 = null;
            DataSet dsSubOut3 = null;
            DataSet dsSubOut4 = null;
            DataSet dsSubOut5 = null;
            DataSet dsSubOut6 = null;
            DataSet dsSubOut7 = null;

            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    sznetcode = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szEndDate = InfoIn[1];
                }


                if ((InfoIn.Length - 1) > 1)
                {
                    szAppPath = InfoIn[2];
                }

                if ((InfoIn.Length - 1) > 2)
                {
                    szValdate = InfoIn[3];
                }

                if ((InfoIn.Length - 1) > 3)
                {
                    szReference = InfoIn[4];
                }
                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];
                szReadInfo[0] = sznetcode;
                szReadInfo[1] = szEndDate;
                szReadInfo[2] = szValdate;
                szReadInfo[3] = szReference;

                //szReadInfo[0] = "CNSHA";
                //szReadInfo[0] = "USCOR";
                //szReadInfo[1] = "10/12/2009";
                //szReadInfo[2] = "10/17/2009";
                //szReadInfo[3] = "October";

                szError = GetDataAsCursorZAJNBRpt1(szConnStr, "spReport1", szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {
                    szError1 = GetDataAsCursorZAJNBRpt1(szConnStr, "spreport2", szReadInfo, ref dsSubOut6);
                    szError2 = GetDataAsCursorZAJNBRpt(szConnStr, "spfxsoffer", szReadInfo, ref dsSubOut1);
                    szError3 = GetDataAsCursorZAJNBRpt(szConnStr, "spReceivables", szReadInfo, ref dsSubOut2);
                    szError4 = GetDataAsCursorZAJNBRpt(szConnStr, "spPayables", szReadInfo, ref dsSubOut3);
                    szError5 = GetDataAsCursorZAJNBRpt(szConnStr, "sptotals", szReadInfo, ref dsSubOut4);
                    szError6 = GetDataAsCursorZAJNBRpt(szConnStr, "spCurrexchange", szReadInfo, ref dsSubOut5);
                    szError7 = GetDataAsCursorZAJNBRpt(szConnStr, "spRecPayPositionCurr", szReadInfo, ref dsSubOut7);

                    if (szError1 == string.Empty && szError2 == string.Empty && szError3 == string.Empty)
                    {
                        rpt = new ReportDocument();
                        strFilename = szReadInfo[0] + System.DateTime.Now.Millisecond + System.DateTime.Now;
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                        strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");


                        report_name = _ReportLocation + "STNEW.rpt";
                        rpt.Load(report_name);
                        rpt.OpenSubreport("Mainsub.rpt");
                        rpt.OpenSubreport("fxoffer.rpt");
                        rpt.OpenSubreport("receivable.rpt");
                        rpt.OpenSubreport("payable.rpt");
                        rpt.OpenSubreport("Total.rpt");
                        rpt.OpenSubreport("ratestable.rpt");
                        rpt.OpenSubreport("PoscurrStation.rpt");
                        rpt.Refresh();

                        if (dsMainOut.Tables.Count == 0)
                        {
                            szError = "No Records Found";
                        }
                        else
                        {
                            rpt.SetDataSource(dsMainOut.Tables[0]);
                            rpt.Subreports["Mainsub.rpt"].SetDataSource(dsSubOut6.Tables[0]);
                            rpt.Subreports["fxoffer.rpt"].SetDataSource(dsSubOut1.Tables[0]);
                            rpt.Subreports["receivable.rpt"].SetDataSource(dsSubOut2.Tables[0]);
                            rpt.Subreports["payable.rpt"].SetDataSource(dsSubOut3.Tables[0]);
                            rpt.Subreports["Total.rpt"].SetDataSource(dsSubOut4.Tables[0]);
                            rpt.Subreports["ratestable.rpt"].SetDataSource(dsSubOut5.Tables[0]);
                            rpt.Subreports["PoscurrStation.rpt"].SetDataSource(dsSubOut7.Tables[0]);
                            rpt.ReportOptions.EnableSaveDataWithReport = true;
                            rpt.Refresh();




                            CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                            rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                            //rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                            //strFilename = strFilename + ".pdf";
                            rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.Excel;
                            strFilename = strFilename + ".xls";
                            //myexportfile = szAppPath + "docs/" + strFilename;
                            // myexportfile = szPath + "docs/" + strFilename;
                            myexportfile = _DocPath + strFilename;
                            DiskOpts.DiskFileName = myexportfile;
                            szReportPathName = myexportfile;
                            rpt.ExportOptions.DestinationOptions = DiskOpts;
                            rpt.Export();
                            rpt.Close();
                            rpt.Dispose();
                            rpt = null;
                            myexportfile = _DocumentURL + strFilename;

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_Station_New) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }
                if (!(dsSubOut1 == null))
                {
                    dsSubOut1.Dispose();
                }
                if (!(dsSubOut2 == null))
                {
                    dsSubOut2.Dispose();
                }
                if (!(dsSubOut3 == null))
                {
                    dsSubOut3.Dispose();
                }
            }
            return szReturn_Array;
        }

        public string[] GenBankCycleRep(string szinfo)
        {
            return GenBankCycleReport(szinfo.Split('|'));
        }

        public string[] GenBankCycleReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];
            string szCycle = string.Empty;


            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;



            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szCycle = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szAppPath = InfoIn[1];
                }



                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];
                szReadInfo[0] = szCycle;


                //szReadInfo[0] = "Dec 08";
                //////szReadInfo[0] = "GBLON";
                //szReadInfo[1] = "11/11/2008";
                //szReadInfo[2] = "11/14/2008";
                //szReadInfo[3] = "OCTOBER";

                szError = GetDataAsCursorBankCycle(szConnStr, "spBankCycle", szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {



                    rpt = new ReportDocument();
                    strFilename = "BC" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");


                    report_name = _ReportLocation + "BankCycle.rpt";
                    rpt.Load(report_name);

                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);

                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();



                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;


                        rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                        strFilename = strFilename + ".pdf";


                        //myexportfile = szAppPath + "docs/" + strFilename;
                        myexportfile = _DocPath + strFilename;

                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        myexportfile = _DocumentURL + strFilename;


                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_BankCycleRep) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        public string[] GenCorporateNettRep(string szinfo)
        {
            return GenCorporateNettReport(szinfo.Split('|'));
        }

        public string[] GenCorporateNettReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];

            string szvaldate = string.Empty;
            string szReference = string.Empty;
            string szEndDate = string.Empty;
            string sztype = string.Empty;
            string szValdate = string.Empty;
            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;



            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szEndDate = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szAppPath = InfoIn[1];
                }


                if ((InfoIn.Length - 1) > 1)
                {
                    szValdate = InfoIn[2];
                }

                if ((InfoIn.Length - 1) > 2)
                {
                    szReference = InfoIn[3];
                }

                if ((InfoIn.Length - 1) > 2)
                {
                    sztype = InfoIn[4];
                }

                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];

                szReadInfo[0] = szEndDate;
                szReadInfo[1] = szValdate;
                szReadInfo[2] = szReference;


                //szReadInfo[0] = "10/12/2009";
                //szReadInfo[1] = "10/14/2009";
                //szReadInfo[2] = "OCTOBER";

                szError = GetDataAsCursorCorRpt(szConnStr, "spNetCorpRptTemp", szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {


                    rpt = new ReportDocument();
                    strFilename = "CORB" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");


                    report_name = _ReportLocation + "CorpNet.rpt";
                    rpt.Load(report_name);

                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);

                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();



                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                        if (sztype == "PDF")
                        {

                            rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                            strFilename = strFilename + ".pdf";
                        }
                        else
                        {
                            rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.Excel;
                            strFilename = strFilename + ".xls";

                        }

                        //myexportfile = szAppPath + "docs/" + strFilename;
                        myexportfile = _DocPath + strFilename;

                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        myexportfile = _DocumentURL + strFilename;


                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_NetRep) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        public string[] GenFxRep(string szinfo)
        {
            return GenFXReport(szinfo.Split('|'));
        }

        public string[] GenFXReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];


            string szBegdate = string.Empty;
            string szEndDate = string.Empty;

            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;



            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szBegdate = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szAppPath = InfoIn[1];
                }


                if ((InfoIn.Length - 1) > 1)
                {
                    szEndDate = InfoIn[2];
                }



                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];

                szReadInfo[0] = szBegdate;
                szReadInfo[1] = szEndDate;



                //szReadInfo[0] = "11/11/2008";
                //szReadInfo[1] = "11/14/2008";


                szError = GetDataAsCursorFXRpt(szConnStr, "spfxsofferRpt", szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {


                    rpt = new ReportDocument();
                    strFilename = "FX" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");


                    report_name = _ReportLocation + "Fxoffer.rpt";
                    rpt.Load(report_name);

                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);

                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();




                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                        rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                        strFilename = strFilename + ".pdf";

                        //myexportfile = szAppPath + "docs/" + strFilename;
                        myexportfile = _DocPath + strFilename;

                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        myexportfile = _DocumentURL + strFilename;


                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_FxRep) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        public string[] GenShippingSalesReport(string InfoIn)
        {
            string[] szReturn_Array = new string[6];


            string szBegdate = string.Empty;
            string szEndDate = string.Empty;

            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;

            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;

            Excel.Application oExcel = new Excel.Application();
            Excel.Workbooks oBooks;
            Excel.Workbook oBook;
            Excel.Sheets oSheets;
            Excel.Worksheet oSheet;
            Excel.Range oCells;
            object misValue = System.Reflection.Missing.Value;



            try
            {

                string szDoc = string.Empty;

                szError = GetDataAsCursorShippingSalesRpt(szConnStr, "spWhseGlobleSurfReportData", InfoIn, ref dsMainOut);
                if (szError == string.Empty)
                {


                    strFilename = "SHP_SALES" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");

                    report_name = _ReportLocation + "GlobalSurf.xls"; //@"D:\etrack_net\JasOrbisSL\wsJas\Reports\GlobalSurf.xls";

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {

                        oExcel.Visible = false;
                        oExcel.DisplayAlerts = false;

                        //Start a new workbook

                        oBooks = oExcel.Workbooks;
                        oBook = oBooks.Open(report_name, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
                        oSheets = oBook.Worksheets;
                        oSheet = (Excel.Worksheet)oSheets.get_Item(1);
                        oCells = oSheet.Cells;
                        DumpDataForGlobalSurfRpt(dsMainOut.Tables[0], oCells); //Fill in the data

                        strFilename = strFilename + ".xls";
                        myexportfile = _DocPath + strFilename;

                        oSheet.SaveAs(myexportfile, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); //Save in a temporary file
                        oBook.Saved = true;
                        oBook.Close(true, misValue, misValue);

                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_ShippingSalesRep) " + ex.Message;
            }
            finally
            {

                //Quit Excel and thoroughly deallocate everything
                oExcel.Quit();
                //releaseObject(oSheet);
                //releaseObject(oBook);
                releaseObject(oExcel);

                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        public string[] GenGSIInventoryReport(string InfoIn)
        {
            string[] szReturn_Array = new string[6];


            string szBegdate = string.Empty;
            string szEndDate = string.Empty;

            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;

            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;

            Excel.Application oExcel = new Excel.Application();
            Excel.Workbooks oBooks;
            Excel.Workbook oBook;
            Excel.Sheets oSheets;
            Excel.Worksheet oSheet;
            Excel.Range oCells;
            object misValue = System.Reflection.Missing.Value;



            try
            {

                string szDoc = string.Empty;

                szError = GetDataAsCursorShippingSalesRpt(szConnStr, "spWhseGlobleSurfInventoryReport", InfoIn, ref dsMainOut);
                if (szError == string.Empty)
                {


                    strFilename = "GSI_INV" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");

                    report_name = _ReportLocation + "GlobalSurf_Inventory.xls"; //@"D:\etrack_net\JasOrbisSL\wsJas\Reports\GlobalSurf_Inventory.xls";

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {

                        oExcel.Visible = false;
                        oExcel.DisplayAlerts = false;

                        //Start a new workbook

                        oBooks = oExcel.Workbooks;
                        oBook = oBooks.Open(report_name, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
                        oSheets = oBook.Worksheets;
                        oSheet = (Excel.Worksheet)oSheets.get_Item(1);
                        oCells = oSheet.Cells;
                        DumpDataForGlobalSurfRpt(dsMainOut.Tables[0], oCells); //Fill in the data

                        strFilename = strFilename + ".xls";
                        myexportfile = _DocPath + strFilename;

                        oSheet.SaveAs(myexportfile, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); //Save in a temporary file
                        oBook.Saved = true;
                        oBook.Close(true, misValue, misValue);

                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_ShippingSalesRep) " + ex.Message;
            }
            finally
            {

                //Quit Excel and thoroughly deallocate everything
                oExcel.Quit();
                //releaseObject(oSheet);
                //releaseObject(oBook);
                releaseObject(oExcel);

                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        private void DumpDataForGlobalSurfRpt(DataTable dt, Excel.Range oCells)
        {
            DataRow dr;
            for (int i = 0; i <= (dt.Rows.Count - 1); i++)
            {
                dr = dt.Rows[i];

                for (int j = 0; j <= (dt.Columns.Count - 1); j++)
                {
                    oCells[i + 3, j + 1] = dr[j].ToString();
                }
            }


        }
        private void DumpDataForGatesInqRpt(DataTable dt, Excel.Range oCells)
        {
            DataRow dr;
            for (int i = 0; i <= (dt.Rows.Count - 1); i++)
            {
                dr = dt.Rows[i];

                for (int j = 0; j <= (dt.Columns.Count - 1); j++)
                {
                    oCells[i + 5, j + 1] = dr[j].ToString();
                }
            }


        }
        private void DumpDataForGatesRpt(DataTable dt, Excel.Range oCells)
        {
            DataRow dr;
            for (int i = 0; i <= (dt.Rows.Count - 1); i++)
            {
                dr = dt.Rows[i];

                for (int j = 0; j <= (dt.Columns.Count - 1); j++)
                {
                    oCells[i + 2, j + 1] = dr[j].ToString();
                }
            }


        }

        #region commentedout
        //private void DumpDataFORBikeEX(DataTable dt, Excel.Range oCells)
        //{

        //    DataRow dr = null;
        //    object[] ary = null;

        //    int iRow = 0;
        //    int iCol = 0;
        //    int iCol1 = 0;
        //    int iRow1 = 0;

        //    for (iRow = 0; iRow <= dt.Rows.Count - 1; iRow++)
        //    {
        //        dr = dt.Rows[iRow];

        //        ary = dr.ItemArray;

        //        int szCnt = 0;
        //        string[] SZDATA = null;

        //        for (iCol = 0; iCol <= (dt.Columns.Count - 1); iCol++)
        //        {
        //            if (iCol == 2)
        //            {
        //                SZDATA = ary[iCol].ToString().Split('|');
        //                szCnt = SZDATA.Length;
        //            }
        //            if (iCol == 2 | iCol == 3 | iCol == 14 | iCol == 15)
        //            {
        //                if (szCnt > 1)
        //                {
        //                    oCells[iRow + 2, iCol + 1] = ary[iCol].ToString().Split('|')[0];

        //                }
        //                else
        //                {
        //                    //Modified By Kavita Arora
        //                    //Date June-16-2011
        //                    // In order to fix bug which was not writing Unit price and buyers parts 
        //                    // to excel
        //                    oCells[iRow + 2, iCol + 1] = ary[iCol].ToString();

        //                }

        //            }
        //            else
        //            {
        //                oCells[iRow + 2, iCol + 1] = ary[iCol].ToString();

        //            }

        //            if (iCol == 15 & szCnt > 1)
        //            {
        //                for (iRow1 = 1; iRow1 <= szCnt - 1; iRow1++)
        //                {

        //                    for (iCol1 = 0; iCol1 <= (dt.Columns.Count - 1); iCol1++)
        //                    {
        //                        if (iCol1 == 2 | iCol1 == 3 | iCol1 == 14 | iCol1 == 15)
        //                        {
        //                            if (szCnt > 1)
        //                            {
        //                                oCells[iRow + 3, iCol1 + 1] = ary[iCol1].ToString().Split('|')[iRow1];

        //                            }


        //                        }
        //                        else
        //                        {
        //                            oCells[iRow + 3, iCol1 + 1] = ary[iCol1].ToString();

        //                        }

        //                    }
        //                }
        //            }
        //            // Response.Write(ary(iCol).ToString & vbTab)

        //        }

        //    }
        //    return;
        //}
        #endregion

        private void DumpDataFORBikeEX(DataTable dt, Excel.Range oCells)
        {

            DataRow dr = null;
            object[] ary = null;
            string sFlag = string.Empty;

            int iRow = 0;
            int iCol = 0;
            int iCol1 = 0;
            int iRow1 = 0;
            sFlag = "N";

            for (iRow = 0; iRow <= dt.Rows.Count - 1; iRow++)
            {
                dr = dt.Rows[iRow];
                ary = dr.ItemArray;

                for (iCol = 0; iCol <= (dt.Columns.Count - 1); iCol++)
                {
                    int szCnt = 0;
                    string[] SZDATA = null;

                    SZDATA = ary[iCol].ToString().Split('|');
                    szCnt = SZDATA.Length;

                    if (iCol == 2 & szCnt > 1)
                    {
                        sFlag = "Y";

                        for (iRow1 = 0; iRow1 <= szCnt - 1; iRow1++)
                        {

                            for (iCol1 = 0; iCol1 <= (dt.Columns.Count - 1); iCol1++)
                            {
                                if (iCol1 == 2 | iCol1 == 3 | iCol1 == 14 | iCol1 == 15)
                                {

                                    oCells[iRow1 + 2, iCol1 + 1] = ary[iCol1].ToString().Split('|')[iRow1];

                                }
                                else
                                {
                                    oCells[iRow1 + 2, iCol1 + 1] = ary[iCol1].ToString();

                                }


                            }


                        }


                    }

                    if (sFlag == "N" & szCnt <= 1)
                    {
                        oCells[iRow + 2, iCol + 1] = ary[iCol].ToString();

                    }

                }
            }


        }

        public string[] GenCycleTransRep(string szinfo)
        {
            return GenCycleTransReport(szinfo.Split('|'));
        }

        public string[] GenCycleTransReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];
            string szBegCycle = string.Empty;
            string szEndCycle = string.Empty;
            string szNetcode = string.Empty;


            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;



            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szBegCycle = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szEndCycle = InfoIn[1];
                }
                if ((InfoIn.Length - 1) > 1)
                {
                    szNetcode = InfoIn[2];
                }
                if ((InfoIn.Length - 1) > 2)
                {
                    szAppPath = InfoIn[3];
                }




                string[] szReadInfo = new string[11];
                szReadInfo[0] = szBegCycle;
                szReadInfo[1] = szEndCycle;
                szReadInfo[2] = szNetcode;
                //szReadInfo[0] = "1/12/2009";
                //szReadInfo[1] = "1/12/2009";
                //szReadInfo[2] = "ALL";
                //szNetcode = "ALL";
                //szReadInfo[0] = "Dec 08";
                //////szReadInfo[0] = "GBLON";
                //szReadInfo[1] = "11/11/2008";
                //szReadInfo[2] = "11/14/2008";
                //szReadInfo[3] = "OCTOBER";
                if (szNetcode == "ALL")
                {
                    szError = GetDataAsCursorCycleTrans(szConnStr, "spCycleTransALL", szReadInfo, ref dsMainOut);

                }
                else
                {
                    szError = GetDataAsCursorCycleTrans(szConnStr, "spCycleTrans", szReadInfo, ref dsMainOut);

                }

                if (szError == string.Empty)
                {



                    rpt = new ReportDocument();
                    strFilename = "SBB" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");


                    report_name = _ReportLocation + "CycleTrans.rpt";
                    rpt.Load(report_name);

                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);

                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();



                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;


                        rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                        strFilename = strFilename + ".pdf";


                        //myexportfile = szAppPath + "docs/" + strFilename;
                        myexportfile = _DocPath + strFilename;

                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        myexportfile = _DocumentURL + strFilename;


                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_CycleTransRep) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        public string[] GenRecPayRep(string szinfo)
        {
            return GenRecPayReport(szinfo.Split('|'));
        }

        public string[] GenRecPayReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];



            string szEndDate = string.Empty;

            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;



            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szEndDate = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szAppPath = InfoIn[1];
                }





                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];

                szReadInfo[0] = szEndDate;




                // szReadInfo[0] = "11/11/2008";
                //szReadInfo[1] = "11/14/2008";


                szError = GetDataAsCursorRecPayRpt(szConnStr, "spRecPayRpt", szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {


                    rpt = new ReportDocument();
                    strFilename = "RecPay" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");


                    report_name = _ReportLocation + "recpay.rpt";
                    rpt.Load(report_name);

                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);

                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();




                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                        rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                        strFilename = strFilename + ".pdf";

                        //myexportfile = szAppPath + "docs/" + strFilename;
                        myexportfile = _DocPath + strFilename;

                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        myexportfile = _DocumentURL + strFilename;


                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_RecPayRep) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        public string[] GenSubNetRep(string szinfo)
        {
            return GenSubNetReport(szinfo.Split('|'));
        }

        public string[] GenSubNetReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];
            string sznetcode = string.Empty;
            string szValdate = string.Empty;
            string szReference = string.Empty;
            string szEndDate = string.Empty;

            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;
            string szError1 = string.Empty;
            string szError2 = string.Empty;
            string szError3 = string.Empty;
            string szError4 = string.Empty;
            string szError5 = string.Empty;
            string szError6 = string.Empty;
            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;
            DataSet dsSubOut1 = null;
            DataSet dsSubOut2 = null;
            DataSet dsSubOut3 = null;
            DataSet dsSubOut4 = null;
            DataSet dsSubOut5 = null;
            DataSet dsSubOut6 = null;


            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    sznetcode = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szEndDate = InfoIn[1];
                }


                if ((InfoIn.Length - 1) > 1)
                {
                    szAppPath = InfoIn[2];
                }

                if ((InfoIn.Length - 1) > 2)
                {
                    szValdate = InfoIn[3];
                }

                if ((InfoIn.Length - 1) > 3)
                {
                    szReference = InfoIn[4];
                }

                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];
                szReadInfo[0] = sznetcode;
                szReadInfo[1] = szEndDate;
                szReadInfo[2] = szValdate;
                szReadInfo[3] = szReference;

                //szReadInfo[0] = "HKCHN";
                //szReadInfo[0] = "CNSGA";
                //szReadInfo[1] = "11/11/2008";
                //szReadInfo[2] = "11/14/2008";
                //szReadInfo[3] = "OCTOBER";

                szError = GetDataAsCursorZAJNBRpt1(szConnStr, "spSubNetParent", szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {

                    szError1 = GetDataAsCursorZAJNBRpt1(szConnStr, "spreport2", szReadInfo, ref dsSubOut6);
                    szError2 = GetDataAsCursorZAJNBRpt(szConnStr, "spfxsoffer", szReadInfo, ref dsSubOut1);
                    szError3 = GetDataAsCursorZAJNBRpt(szConnStr, "spReceivables", szReadInfo, ref dsSubOut2);
                    szError4 = GetDataAsCursorZAJNBRpt(szConnStr, "spPayables", szReadInfo, ref dsSubOut3);
                    szError5 = GetDataAsCursorZAJNBRpt(szConnStr, "sptotals", szReadInfo, ref dsSubOut4);
                    szError6 = GetDataAsCursorZAJNBRpt(szConnStr, "spCurrexchange", szReadInfo, ref dsSubOut5);





                    rpt = new ReportDocument();
                    strFilename = "SUBNET" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");


                    report_name = _ReportLocation + "subNetorg.rpt";
                    rpt.Load(report_name);


                    rpt.OpenSubreport("Mainsub.rpt");
                    rpt.OpenSubreport("fxoffer.rpt");
                    rpt.OpenSubreport("receivable.rpt");
                    rpt.OpenSubreport("payable.rpt");
                    rpt.OpenSubreport("Total.rpt");
                    rpt.OpenSubreport("ratestable.rpt");
                    rpt.Refresh();













                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);
                        rpt.Subreports["Mainsub.rpt"].SetDataSource(dsSubOut6.Tables[0]);
                        rpt.Subreports["fxoffer.rpt"].SetDataSource(dsSubOut1.Tables[0]);
                        rpt.Subreports["receivable.rpt"].SetDataSource(dsSubOut2.Tables[0]);
                        rpt.Subreports["payable.rpt"].SetDataSource(dsSubOut3.Tables[0]);
                        rpt.Subreports["Total.rpt"].SetDataSource(dsSubOut4.Tables[0]);
                        rpt.Subreports["ratestable.rpt"].SetDataSource(dsSubOut5.Tables[0]);
                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();



                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;


                        rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                        strFilename = strFilename + ".pdf";


                        //myexportfile = szAppPath + "docs/" + strFilename;
                        myexportfile = _DocPath + strFilename;

                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        myexportfile = _DocumentURL + strFilename;


                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_SubNetRep) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        public string[] GenSubPayRep(string szinfo)
        {
            return GenSubPayReport(szinfo.Split('|'));
        }

        public string[] GenSubPayReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];

            string szvaldate = string.Empty;
            string szReference = string.Empty;
            string szEndDate = string.Empty;
            string sztype = string.Empty;
            string szValdate = string.Empty;
            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;



            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szEndDate = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szAppPath = InfoIn[1];
                }


                if ((InfoIn.Length - 1) > 1)
                {
                    szValdate = InfoIn[2];
                }

                if ((InfoIn.Length - 1) > 2)
                {
                    szReference = InfoIn[3];
                }

                if ((InfoIn.Length - 1) > 2)
                {
                    sztype = InfoIn[4];
                }

                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];

                szReadInfo[0] = szEndDate;
                szReadInfo[1] = szValdate;
                szReadInfo[2] = szReference;


                //szReadInfo[0] = "11/11/2008";
                //szReadInfo[1] = "11/14/2008";
                //szReadInfo[2] = "OCTOBER";

                szError = GetDataAsCursorCorRpt(szConnStr, "spsubPay", szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {


                    rpt = new ReportDocument();
                    strFilename = "SUBPAY" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");


                    report_name = _DocPath + "subPay.rpt";
                    rpt.Load(report_name);

                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);

                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();



                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                        if (sztype == "PDF")
                        {

                            rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                            strFilename = strFilename + ".pdf";
                        }
                        else
                        {
                            rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.Excel;
                            strFilename = strFilename + ".xls";

                        }

                        //myexportfile = szAppPath + "docs/" + strFilename;
                        myexportfile = _DocPath + strFilename;

                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        myexportfile = _DocumentURL + strFilename;


                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_NetRep) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

   

        public string[] GenerateSTBANKRep(string szinfo)
        {
            return GenerateSTBANKReport(szinfo.Split('|'));
        }

        public string[] GenerateSTBANKReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];

            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;



            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szAppPath = InfoIn[0];
                }

                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];


                szError = GetDataAsCursorSTBANKRpt(szConnStr, "spstationbank", szReadInfo, ref dsMainOut);
                if (szError == string.Empty)
                {



                    rpt = new ReportDocument();
                    strFilename = "STBANK" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");


                    report_name = _ReportLocation + "JasStbank.rpt";
                    rpt.Load(report_name);

                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);

                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();




                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                        rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                        strFilename = strFilename + ".pdf";

                        //myexportfile = szAppPath + "docs/" + strFilename;
                        myexportfile = _DocPath + strFilename;

                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        myexportfile = _DocumentURL + strFilename;

                    }
                }

            }
            catch (Exception ex)
            {
                szError = "(Generate_STBANK) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        public string[] GenTrendRep(string szinfo)
        {
            return GenTrendReport(szinfo.Split('|'));
        }

        public string[] GenTrendReport(string[] InfoIn)
        {
            string[] szReturn_Array = new string[6];
            string szBegCycle = string.Empty;
            string szEndCycle = string.Empty;
            string szNetcode = string.Empty;

            string sztype = string.Empty;
            string szReportPathName = string.Empty;
            string szConnStr = Properties.Settings.Default.ConnectionString;
            string szAppPath = string.Empty;
            string myexportfile = string.Empty;
            string szError = string.Empty;

            string report_name = string.Empty;
            ReportDocument rpt = new ReportDocument();
            string szRptDelim = string.Empty;
            string strFilename = string.Empty;
            string strFilenames = string.Empty;
            DataSet dsMainOut = null;



            try
            {
                if ((InfoIn.Length - 1) > -1)
                {
                    szBegCycle = InfoIn[0];
                }
                if ((InfoIn.Length - 1) > 0)
                {
                    szEndCycle = InfoIn[1];
                }
                if ((InfoIn.Length - 1) > 1)
                {
                    szAppPath = InfoIn[2];
                }

                if ((InfoIn.Length - 1) > 2)
                {
                    sztype = InfoIn[3];
                }


                string[] szReadInfo = new string[11];
                szReadInfo[0] = szBegCycle;
                szReadInfo[1] = szEndCycle;

                //szReadInfo[0] = "1/12/2009";
                //szReadInfo[1] = "1/12/2009";


                szError = GetDataAsTrendRep(szConnStr, "spTrendRep", szReadInfo, ref dsMainOut);



                if (szError == string.Empty)
                {



                    rpt = new ReportDocument();
                    strFilename = "NTR" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, "/", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, " ", "");
                    strFilename = System.Text.RegularExpressions.Regex.Replace(strFilename, ":", "");


                    report_name = _ReportLocation + "NettingTrend.rpt";
                    rpt.Load(report_name);

                    rpt.Refresh();

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {
                        rpt.SetDataSource(dsMainOut.Tables[0]);

                        rpt.ReportOptions.EnableSaveDataWithReport = true;
                        rpt.Refresh();



                        CrystalDecisions.Shared.DiskFileDestinationOptions DiskOpts = new CrystalDecisions.Shared.DiskFileDestinationOptions();
                        rpt.ExportOptions.ExportDestinationType = @CrystalDecisions.Shared.ExportDestinationType.DiskFile;
                        if (sztype == "PDF")
                        {

                            rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
                            strFilename = strFilename + ".pdf";
                        }
                        else
                        {
                            rpt.ExportOptions.ExportFormatType = @CrystalDecisions.Shared.ExportFormatType.Excel;
                            strFilename = strFilename + ".xls";

                        }


                        //myexportfile = szAppPath + "docs/" + strFilename;
                        myexportfile = _DocPath + strFilename;

                        DiskOpts.DiskFileName = myexportfile;
                        szReportPathName = myexportfile;
                        rpt.ExportOptions.DestinationOptions = DiskOpts;
                        rpt.Export();
                        rpt.Close();
                        rpt.Dispose();
                        rpt = null;
                        myexportfile = _DocumentURL + strFilename;


                    }
                }
            }
            catch (Exception ex)
            {
                szError = "(Generate_spTrendRep) " + ex.Message;
            }
            finally
            {
                szReturn_Array[0] = szError;
                szReturn_Array[1] = szReportPathName;
                szReturn_Array[2] = myexportfile;

                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }

        public static string GetDataAsCursorCorRpt(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;


            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;

                param = new System.Data.SqlClient.SqlParameter("@enddate", SqlDbType.NVarChar, 20, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[0];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@valuedate", SqlDbType.NVarChar, 20, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[1];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@Reference", SqlDbType.NVarChar, 500, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[2];
                @params.Add(param);

                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }
        public static string GetDataAsCursorCycleTrans(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;


            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;
                param = new System.Data.SqlClient.SqlParameter("@Begcycle", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[0];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@Endcycle", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[1];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@netcode", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[2];
                @params.Add(param);


                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;
                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }
        public static string GetDataAsCursorRecPayRpt(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;


            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;


                param = new System.Data.SqlClient.SqlParameter("@enddate", SqlDbType.NVarChar, 20, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[0];
                @params.Add(param);

                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }
        public static string GetDataAsCursorBankCycle(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;


            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;
                param = new System.Data.SqlClient.SqlParameter("@cycle", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[0];
                @params.Add(param);


                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;
                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }
        public static string GetDataAsTrendRep(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;


            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;
                param = new System.Data.SqlClient.SqlParameter("@Begcycle", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[0];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@Endcycle", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[1];
                @params.Add(param);


                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;
                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }
        public static string GetDataAsCursorSTBANKRpt(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;


            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                //List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                //System.Data.SqlClient.SqlParameter param;
                //param = new System.Data.SqlClient.SqlParameter("@netcode", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                //param.Value = szInfoIn[0];
                //@params.Add(param);
                //param = new System.Data.SqlClient.SqlParameter("@enddate", SqlDbType.NVarChar, 20, ParameterDirection.Input.ToString());
                //param.Value = szInfoIn[1];
                //@params.Add(param);
                //param = new System.Data.SqlClient.SqlParameter("@valuedate", SqlDbType.NVarChar, 20, ParameterDirection.Input.ToString());
                //param.Value = szInfoIn[2];
                //@params.Add(param);
                //param = new System.Data.SqlClient.SqlParameter("@Reference", SqlDbType.NVarChar, 500, ParameterDirection.Input.ToString());
                //param.Value = szInfoIn[3];
                //@params.Add(param);

                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                //foreach (SqlParameter param1 in @params)
                //{
                //    cmd.Parameters.Add(param1);
                //}

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }
        public static string GetDataAsCursorFXRpt(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;


            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;

                param = new System.Data.SqlClient.SqlParameter("@begdate", SqlDbType.NVarChar, 20, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[0];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@enddate", SqlDbType.NVarChar, 20, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[1];
                @params.Add(param);

                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }
        public static string GetDataAsCursorGatesPOINQRpt(string szConnStr, string szOSPName, string szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;


            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;

                System.Guid SessionID = new Guid("1D133273-B7C4-4E93-9994-71830EDF2692");


                param = new System.Data.SqlClient.SqlParameter("@SessionID", SqlDbType.UniqueIdentifier, 16, ParameterDirection.Input.ToString());
                param.Value = SessionID;
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@Customer", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString());
                param.Value = "19150";
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@DateType", SqlDbType.VarChar, 20, ParameterDirection.Input.ToString());
                param.Value = "PO Date";
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@DateFrom", SqlDbType.Date, 20, ParameterDirection.Input.ToString());
                param.Value = "1754-01-01";
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@DateTo", SqlDbType.Date, 20, ParameterDirection.Input.ToString());
                param.Value = "9999-12-31";
                @params.Add(param);



                param = new System.Data.SqlClient.SqlParameter("@Supplier", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString());
                param.Value = null;
                @params.Add(param);


                param = new System.Data.SqlClient.SqlParameter("@CustomerNo", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString());
                param.Value = "4303";
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@PartNo", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString());
                param.Value = null;
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@OrderNo", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString());
                param.Value = null;
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@Category", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString());
                param.Value = "-ReportData";
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@FactoryCode", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString());
                param.Value = null;
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@ReceiptNo", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString());
                param.Value = null;
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@CustomerPO", SqlDbType.VarChar, 100, ParameterDirection.Input.ToString());
                param.Value = null;
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@GraphFlag", SqlDbType.Bit, 1, ParameterDirection.Input.ToString());
                param.Value = 0;
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@RowFrom", SqlDbType.Int, 10, ParameterDirection.Input.ToString());
                param.Value = 1;
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@RowTo", SqlDbType.Int, 10, ParameterDirection.Input.ToString());
                param.Value = 50;
                @params.Add(param);

                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }
        public static string GetDataAsCursorShippingSalesRpt(string szConnStr, string szOSPName, string szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;


            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;

                param = new System.Data.SqlClient.SqlParameter("@Id", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn;
                @params.Add(param);

                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }

        public static string GetDataAsCursorBooking(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;


            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;
                param = new System.Data.SqlClient.SqlParameter("@Bookid", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[0];
                @params.Add(param);



                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }

        public static string GetDataAsCursor(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;

            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;
                param = new System.Data.SqlClient.SqlParameter("@Type", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[0];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@WHNO", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[1];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@RCNO", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[2];
                @params.Add(param);


                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();

            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;

        }
        public static string GetDataAsCursorTrnRpt(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {
            string szError = string.Empty;
            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;
                param = new System.Data.SqlClient.SqlParameter("@Account", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[0];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@partno", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[1];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@reference", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[2];
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@cutoffdate", SqlDbType.NVarChar, 20, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[3];
                @params.Add(param);

                param = new System.Data.SqlClient.SqlParameter("@summary", SqlDbType.NVarChar, 500, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[4];
                @params.Add(param);

                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();

            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }
            return szError;
        }
        public static string GetDataAsCursorZAJNBRpt1(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;


            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;
                param = new System.Data.SqlClient.SqlParameter("@netcode", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[0];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@enddate", SqlDbType.NVarChar, 20, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[1];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@valuedate", SqlDbType.NVarChar, 20, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[2];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@Reference", SqlDbType.NVarChar, 500, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[3];
                @params.Add(param);

                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;
                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }
        public static string GetDataAsCursorZAJNBRpt(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;


            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;
                param = new System.Data.SqlClient.SqlParameter("@netcode", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[0];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@enddate", SqlDbType.NVarChar, 20, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[1];
                @params.Add(param);


                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;
                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }

        public static string GetDataAsCursorOrbisRpt(string szConnStr, string szSPName, string[] szInfoIn, string szReport, ref DataSet dsOut)
        {

            string szError = string.Empty;
            Guid key;

            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;

                param = new System.Data.SqlClient.SqlParameter("@LoginID", SqlDbType.NVarChar, 20, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[0];
                @params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@Key", SqlDbType.UniqueIdentifier);
                key = new Guid(szInfoIn[1]);
                param.Value = key;
                @params.Add(param);
                if (szReport == "AWB" || szReport == "BOL")
                {
                    param = new System.Data.SqlClient.SqlParameter("@Type", SqlDbType.NVarChar, 1, ParameterDirection.Input.ToString());
                    param.Value = szInfoIn[2];
                    @params.Add(param);
                }

                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();

            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }
        public static string GetDataAsCursorlcsd(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;

            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;
                param = new System.Data.SqlClient.SqlParameter("@PODIVCODE", SqlDbType.NVarChar, 10, ParameterDirection.Input.ToString());
                param.Value = szInfoIn[0];
                @params.Add(param);


                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }


                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();

            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;

        }

        public static string GetDataAsCursorGLS(string szConnStr, string szOSPName, string[] szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;

            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;

                param = new System.Data.SqlClient.SqlParameter("@Booking_ID", SqlDbType.UniqueIdentifier);
                param.Value = new Guid(szInfoIn[0]);
                @params.Add(param);

                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }


                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();

            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;

        }

        public static string GetDataAsCursorGatesTwnRecRpt(string szConnStr, string szOSPName, string szInfoIn, ref DataSet dsOut)
        {

            string szError = string.Empty;


            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;

                param = new System.Data.SqlClient.SqlParameter("@CINV_HDR_NO", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString());
                param.Value = szInfoIn;
                @params.Add(param);

                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szOSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();



            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }
        public string GetDataAsSIRpt(string szConnStr, string szSPName, string[] szInfoIn, string szReport, ref DataSet dsOut)
        {

            string szError = string.Empty;
            Guid key;

            try
            {
                DataSet ds = null;

                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(szConnStr);
                cn.Open();
                List<System.Data.SqlClient.SqlParameter> @params = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter param;

                //param = new System.Data.SqlClient.SqlParameter("@LoginID", SqlDbType.NVarChar, 20, ParameterDirection.Input.ToString());
                //param.Value = szInfoIn[0];
                //@params.Add(param);
                param = new System.Data.SqlClient.SqlParameter("@Key", SqlDbType.UniqueIdentifier);
                key = new Guid(szInfoIn[0]);
                param.Value = key;
                @params.Add(param);

                SqlDataAdapter da;
                SqlCommand cmd = new SqlCommand(szSPName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                foreach (SqlParameter param1 in @params)
                {
                    cmd.Parameters.Add(param1);
                }

                da = new SqlDataAdapter(cmd);

                ds = new DataSet();
                da.Fill(ds);
                dsOut = ds;
                cmd.Dispose();
                da.Dispose();
                cn.Close();

            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Other error -->" + ex.Message);
                szError = ex.Message;
            }

            return szError;


        }

        public void GetDataForDailyShipmentRpt(string sId, string szConnStr, ref DataSet dsOut, DataSet dsCols)
        {
            SqlConnection Conn = new SqlConnection(szConnStr);
            DataSet dsShip = new DataSet();
            DataSet dsShipOut = new DataSet();
            DataTable Table1 = new DataTable("Shipment");
            DataColumn Column1;
            DataRow row1;

            int n;

            try
            {
                SqlCommand command = new SqlCommand("spDynamicWeaklyRpt", Conn);
                command.Parameters.AddWithValue("@Id", sId);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = 250;
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dsShip);

                if (dsShip.Tables.Count > 0)
                {

                    for (n = 1; n <= dsCols.Tables[0].Rows.Count; n = n + 1)
                    {
                        Column1 = new DataColumn("Column" + n.ToString());
                        Table1.Columns.Add(Column1);
                    }




                    if (dsCols.Tables.Count > 0)
                    {


                        foreach (DataRow rowShipment in dsShip.Tables[0].Rows)
                        {

                            row1 = Table1.NewRow();
                            n = 0;

                            foreach (DataRow row in dsCols.Tables[0].Rows)
                            {
                                n = n + 1;
                                if (n <= dsCols.Tables[0].Rows.Count)
                                {
                                    switch (row["report_column_desc"].ToString())
                                    {
                                        case "Account#":
                                            row1["Column" + n.ToString()] = rowShipment["Column1"].ToString();
                                            break;
                                        case "DeliveryDate":
                                            row1["Column" + n.ToString()] = rowShipment["Column2"].ToString();
                                            break;
                                        case "OFD":
                                            row1["Column" + n.ToString()] = rowShipment["Column3"].ToString();
                                            break;
                                        case "CBM":
                                            row1["Column" + n.ToString()] = rowShipment["Column4"].ToString();
                                            break;
                                        case "CreatedDT":
                                            row1["Column" + n.ToString()] = rowShipment["Column5"].ToString();
                                            break;
                                        case "Arrival @ Final Port":
                                            row1["Column" + n.ToString()] = rowShipment["Column6"].ToString();
                                            break;
                                        case "OrgCtry":
                                            row1["Column" + n.ToString()] = rowShipment["Column7"].ToString();
                                            break;
                                        case "DestCtry":
                                            row1["Column" + n.ToString()] = rowShipment["Column8"].ToString();
                                            break;
                                        case "ReadyDT":
                                            row1["Column" + n.ToString()] = rowShipment["Column9"].ToString();
                                            break;
                                        case "File#":
                                            row1["Column" + n.ToString()] = rowShipment["Column10"].ToString();
                                            break;
                                        case "CHB File Number":
                                            row1["Column" + n.ToString()] = rowShipment["Column11"].ToString();
                                            break;
                                        case "Chg Type":
                                            row1["Column" + n.ToString()] = rowShipment["Column12"].ToString();
                                            break;
                                        case "CustomsDT":
                                            row1["Column" + n.ToString()] = rowShipment["Column13"].ToString();
                                            break;
                                        case "CustomsInfo":
                                            row1["Column" + n.ToString()] = rowShipment["Column14"].ToString();
                                            break;
                                        case "Notes":
                                            row1["Column" + n.ToString()] = rowShipment["Column15"].ToString();
                                            break;
                                        case "Container":
                                            row1["Column" + n.ToString()] = rowShipment["Column16"].ToString();
                                            break;
                                        case "ContSize":
                                            row1["Column" + n.ToString()] = rowShipment["Column17"].ToString();
                                            break;
                                        case "CsgCity":
                                            row1["Column" + n.ToString()] = rowShipment["Column18"].ToString();
                                            break;
                                        case "CsgName":
                                            row1["Column" + n.ToString()] = rowShipment["Column19"].ToString();
                                            break;
                                        case "ContMode":
                                            row1["Column" + n.ToString()] = rowShipment["Column20"].ToString();
                                            break;
                                        case "DeliveryLocation":
                                            row1["Column" + n.ToString()] = rowShipment["Column24"].ToString();
                                            break;
                                        case "DepartsOrigin":
                                            row1["Column" + n.ToString()] = rowShipment["Column26"].ToString();
                                            break;
                                        case "ETA":
                                            row1["Column" + n.ToString()] = rowShipment["Column27"].ToString();
                                            break;
                                        case "Received":
                                            row1["Column" + n.ToString()] = rowShipment["Column29"].ToString();
                                            break;
                                        case "DestPort":
                                            row1["Column" + n.ToString()] = rowShipment["Column28"].ToString();
                                            break;
                                        case "EntryNo":
                                            row1["Column" + n.ToString()] = rowShipment["Column33"].ToString();
                                            break;
                                        case "Hawb#":
                                            row1["Column" + n.ToString()] = rowShipment["Column43"].ToString();
                                            break;
                                        case "LOB":
                                            row1["Column" + n.ToString()] = rowShipment["Column47"].ToString();
                                            break;
                                        case "Master#":
                                            row1["Column" + n.ToString()] = rowShipment["Column52"].ToString();
                                            break;
                                        case "OrgPort":
                                            row1["Column" + n.ToString()] = rowShipment["Column59"].ToString();
                                            break;
                                        case "PONum":
                                            row1["Column" + n.ToString()] = rowShipment["Column60"].ToString();
                                            break;
                                        case "Part#":
                                            row1["Column" + n.ToString()] = rowShipment["Column62"].ToString();
                                            break;
                                        case "PartQty":
                                            row1["Column" + n.ToString()] = rowShipment["Column63"].ToString();
                                            break;
                                        case "Pcs":
                                            row1["Column" + n.ToString()] = rowShipment["Column65"].ToString();
                                            break;
                                        case "Invoice#":
                                            row1["Column" + n.ToString()] = rowShipment["Column66"].ToString();
                                            break;
                                        case "RefNum":
                                            row1["Column" + n.ToString()] = rowShipment["Column68"].ToString();
                                            break;
                                        case "DocDate":
                                            row1["Column" + n.ToString()] = rowShipment["Column71"].ToString();
                                            break;
                                        case "Shipper":
                                            row1["Column" + n.ToString()] = rowShipment["Column72"].ToString();
                                            break;
                                        case "ShpName":
                                            row1["Column" + n.ToString()] = rowShipment["Column73"].ToString();
                                            break;
                                        case "VoyageNo":
                                            row1["Column" + n.ToString()] = rowShipment["Column74"].ToString();
                                            break;
                                        case "ShipmentStatus":
                                            row1["Column" + n.ToString()] = rowShipment["Column75"].ToString();
                                            break;
                                        case "ContainerLoaded":
                                            row1["Column" + n.ToString()] = rowShipment["Column76"].ToString();
                                            break;
                                        case "ETA_CLE":
                                            row1["Column" + n.ToString()] = rowShipment["Column77"].ToString();
                                            break;
                                        case "1stPortOfArrival":
                                            row1["Column" + n.ToString()] = rowShipment["Column78"].ToString();
                                            break;
                                        case "TransMode":
                                            row1["Column" + n.ToString()] = rowShipment["Column79"].ToString();
                                            break;
                                        case "Booked(BKG)":
                                            row1["Column" + n.ToString()] = rowShipment["Column80"].ToString();
                                            break;
                                        case "BookAuthorized(BAM)":
                                            row1["Column" + n.ToString()] = rowShipment["Column81"].ToString();
                                            break;
                                        case "BookReadyDT(RDT)":
                                            row1["Column" + n.ToString()] = rowShipment["Column82"].ToString();
                                            break;
                                        case "ETA_BRO":
                                            row1["Column" + n.ToString()] = rowShipment["Column83"].ToString();
                                            break;
                                        case "ETA_LGB_LAX":
                                            row1["Column" + n.ToString()] = rowShipment["Column84"].ToString();
                                            break;
                                        case "Docs":
                                            row1["Column" + n.ToString()] = rowShipment["Column85"].ToString();
                                            break;
                                        case "Vessel":
                                            row1["Column" + n.ToString()] = rowShipment["Column86"].ToString();
                                            break;
                                        case "Wgt":
                                            row1["Column" + n.ToString()] = rowShipment["Column87"].ToString();
                                            break;
                                        case "Commercial Invoice Value":
                                            row1["Column" + n.ToString()] = rowShipment["Column88"].ToString();
                                            break;
                                        case "Commodity":
                                            row1["Column" + n.ToString()] = rowShipment["Column89"].ToString();
                                            break;
                                        //TW: DSR ticket 07/01/2013
                                        case "PackingList#":
                                            row1["Column" + n.ToString()] = rowShipment["Column90"].ToString();
                                            break;
                                        case "Customer Ref#":
                                            row1["Column" + n.ToString()] = rowShipment["Column91"].ToString();
                                            break;
                                        case "Part Description":
                                            row1["Column" + n.ToString()] = rowShipment["Column92"].ToString();
                                            break;
                                        case "Track#":
                                            row1["Column" + n.ToString()] = rowShipment["Column93"].ToString();
                                            break;
                                        case "Due Date":
                                            row1["Column" + n.ToString()] = rowShipment["Column94"].ToString();
                                            break;
                                        //TW: DSR ticket 63412 - KION:  07/30/2013
                                        case "Act. Received at Port":
                                            row1["Column" + n.ToString()] = rowShipment["Column95"].ToString();
                                            break;
                                        case "Est. Received at Port":
                                            row1["Column" + n.ToString()] = rowShipment["Column96"].ToString();
                                            break;
                                        case "20F":
                                            row1["Column" + n.ToString()] = rowShipment["Column97"].ToString();
                                            break;
                                        case "40F":
                                            row1["Column" + n.ToString()] = rowShipment["Column98"].ToString();
                                            break;
                                        case "40H":
                                            row1["Column" + n.ToString()] = rowShipment["Column99"].ToString();
                                            break;
                                        case "TEU":
                                            row1["Column" + n.ToString()] = rowShipment["Column100"].ToString();
                                            break;

                                        //TW: DSR ticket 3-  61984   08/05/2013
                                        case "Est. Departure":
                                            row1["Column" + n.ToString()] = rowShipment["Column101"].ToString();
                                            break;
                                        case "Act. Departure":
                                            row1["Column" + n.ToString()] = rowShipment["Column106"].ToString();
                                            break;
                                        case "Est. Arrival at Dest":
                                            row1["Column" + n.ToString()] = rowShipment["Column102"].ToString();
                                            break;
                                        case "Act. Arrival at Dest":
                                            row1["Column" + n.ToString()] = rowShipment["Column103"].ToString();
                                            break;
                                        case "Est. Arrival at Port/Airport":
                                            row1["Column" + n.ToString()] = rowShipment["Column104"].ToString();
                                            break;
                                        case "Act. Arrival at Port/Airport":
                                            row1["Column" + n.ToString()] = rowShipment["Column105"].ToString();
                                            break;
                                    }
                                }
                            }

                            Table1.Rows.Add(row1);
                        }
                        dsShipOut.Tables.Add(Table1);
                    }

                }
                dsOut = dsShipOut;
                //dsOut.Tables[0].Merge(Table1);

                //objReport.SetDataSource(Ds.Tables["Shipment"]);
            }
            catch (Exception Ex)
            {
                System.Diagnostics.Debug.WriteLine("Error In GetDataForDailyShipmentRpt -->" + Ex.Message);
                Conn.Close();
            }

        }

        //Get Cols For LoginId 
        private DataSet GetDailyShipmentRptColsForLoginId(string sLoginId, string szConnStr)
        {

            DataSet dsCols = new DataSet();
            SqlConnection Conn = new SqlConnection(szConnStr);

            try
            {
                SqlCommand command = new SqlCommand("spGetRptColsForLoginId", Conn);
                command.Parameters.AddWithValue("@Id", sLoginId);
                command.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dsCols);

            }
            catch (Exception Ex)
            {
                System.Diagnostics.Debug.WriteLine("Error In GetDailyShipmentRptColsForLoginId -->" + Ex.Message);
                Conn.Close();
            }

            return dsCols;

        }

        public string CreateParameters(string sLoginId, string szConnStr, ref ReportDocument objReport, DataSet dsCols)
        {
            ParameterFields paramFields;
            paramFields = new ParameterFields();

            ParameterField paramField;
            ParameterDiscreteValue paramDiscreteValue;
            ParameterValues paramValues;

            string sColName = string.Empty;
            string sErr = string.Empty;

            //Intialize ParamsFields
            int columnNo = 0;

            //DataSet dsCols = GetDailyShipmentRptColsForLoginId(sLoginId, szConnStr);

            if (dsCols.Tables.Count > 0)
            {
                for (int iColCount = 1; iColCount <= 10; iColCount++)
                {
                    //columnNo++;
                    sColName = "col" + iColCount.ToString();
                    paramField = new ParameterField();
                    paramField.Name = sColName;
                    paramDiscreteValue = new ParameterDiscreteValue();
                    paramDiscreteValue.Value = "None";
                    objReport.ParameterFields[sColName].CurrentValues.Add(paramDiscreteValue);
                }


                foreach (DataRow row in dsCols.Tables[0].Rows)
                {
                    columnNo++;
                    if (columnNo <= 10)
                    {
                        //paramField = new ParameterField();
                        //paramField.Name = "col" + columnNo.ToString();
                        paramDiscreteValue = new ParameterDiscreteValue();
                        paramValues = new ParameterValues();

                        sColName = "col" + columnNo.ToString();
                        paramDiscreteValue.Value = row["DisplayName"].ToString();
                        paramValues.Add(paramDiscreteValue);
                        objReport.DataDefinition.ParameterFields[sColName].ApplyCurrentValues(paramValues);

                    }

                }

            }
            else
            {
                sErr = "Record Not Found";
            }

            return sErr;
        }

        public void UpdateColWidth(DataSet dsCols, ref ReportDocument objReport)
        {
            string sColName = string.Empty;
            string sparmColName = string.Empty;
            int columnNo = 0;
            int iLeft = 0;
            int iLastPos = 0;

            if (dsCols.Tables.Count > 0)
            {
                foreach (DataRow row in dsCols.Tables[0].Rows)
                {
                    columnNo++;
                    if (columnNo <= 10)
                    {
                        //Data Column
                        FieldObject field;
                        FieldObject parmfield;

                        sparmColName = "col" + columnNo.ToString();
                        sColName = "Column" + columnNo.ToString();

                        field = objReport.ReportDefinition.ReportObjects[sColName] as FieldObject;
                        parmfield = objReport.ReportDefinition.ReportObjects[sparmColName] as FieldObject;

                        field.Width = int.Parse(row["DisplayLength"].ToString());
                        parmfield.Width = int.Parse(row["DisplayLength"].ToString());

                        field.ObjectFormat.HorizontalAlignment = Alignment.LeftAlign;
                        parmfield.ObjectFormat.HorizontalAlignment = Alignment.LeftAlign;

                        if (row["DisplayName"].ToString() == "Weight")
                        {
                            field.ObjectFormat.HorizontalAlignment = Alignment.RightAlign;
                            parmfield.ObjectFormat.HorizontalAlignment = Alignment.RightAlign;
                        }
                        if (row["DisplayName"].ToString() == "Commercial Invoice Value")
                        {
                            field.ObjectFormat.HorizontalAlignment = Alignment.LeftAlign;
                            parmfield.ObjectFormat.HorizontalAlignment = Alignment.LeftAlign;
                        }

                        if (columnNo == 1)
                        {
                            iLeft = int.Parse(row["DisplayLength"].ToString());
                            iLastPos = iLeft;
                        }
                        if (columnNo > 1)
                        {
                            iLeft = iLastPos;
                            field.Left = iLeft;
                            parmfield.Left = iLeft;
                            if (row["DisplayName"].ToString() == "Weight")
                            {
                                iLastPos = iLeft + int.Parse(row["DisplayLength"].ToString()) + 150;
                            }
                            else
                            {
                                iLastPos = iLeft + int.Parse(row["DisplayLength"].ToString());
                            }
                        }



                    }

                }

            }
        }

        public string[] GetGLSExcelReports(string ReportType, int fileID)
        {
            string[] sReturn_Array = new string[6];
            string sError = string.Empty;
            string filename = string.Empty;
            string fileLocation = string.Empty;
            string ConnStr = Properties.Settings.Default.ConStrJasFilesDB;
            SqlConnection Conn = new SqlConnection(ConnStr);
            DataSet ds = new DataSet();
            string strData = string.Empty;

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range chartRange = null;
            object misValue = System.Reflection.Missing.Value;
            int rowNo = 7;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(Missing.Value);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            try
            {


                SqlCommand command = new SqlCommand("uspFILOUTGetFiles", Conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Acct", "GLSLOGISTICS");
                command.Parameters.AddWithValue("@Type", ReportType);
                command.Parameters.AddWithValue("@Fileid", fileID);


                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds);


                if (ds.Tables.Count > 0)
                {

                    if (ds.Tables[0].Rows.Count == 0)
                    {
                        sError = "No Records Found";
                    }
                    else
                    {

                        strData = ds.Tables[0].Rows[0]["FileData"].ToString();
                        filename = ds.Tables[0].Rows[0]["FileName"].ToString();
                        fileLocation = ds.Tables[0].Rows[0]["FilePath"].ToString();
                        string[] lines = strData.Split(new string[] { "\r\n" }, StringSplitOptions.None);

                        if (lines.Length > 0)
                        {
                            //Add Image

                            string _stPicture = Properties.Settings.Default.ImagePath;//"D:\\etrack_net\\JasOrbisSL\\wsJas\\jasorbis1.png";
                            xlWorkSheet.Shapes.AddPicture(_stPicture, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 200, 10, 150, 50);


                            //Add Report Title

                            xlWorkSheet.Cells[3, 2] = ReportType;
                            chartRange = (Excel.Range)xlWorkSheet.Cells[3, 2];
                            chartRange.Font.Bold = true;
                            chartRange.Font.Size = 26;
                            chartRange.Font.Name = "Arial";
                            chartRange.Font.Color = System.Drawing.Color.Red.ToArgb();


                            chartRange = xlApp.ActiveCell;
                            chartRange.EntireRow.WrapText = false;

                            //Add Report Columns

                            for (int columnNo = 1; columnNo <= 10; columnNo++)
                            {
                                switch (columnNo)
                                {
                                    case 1:
                                        xlWorkSheet.Cells[7, columnNo] = "LOA";
                                        break;
                                    case 2:
                                        xlWorkSheet.Cells[7, columnNo] = "On Hand #";
                                        break;
                                    case 3:
                                        xlWorkSheet.Cells[7, columnNo] = "Reference Number";
                                        break;
                                    case 4:
                                        xlWorkSheet.Cells[7, columnNo] = "Part No Desc";
                                        break;
                                    case 5:
                                        xlWorkSheet.Cells[7, columnNo] = "Date Received";
                                        break;
                                    case 6:
                                        xlWorkSheet.Cells[7, columnNo] = "Qty Rec";
                                        break;
                                    case 7:
                                        xlWorkSheet.Cells[7, columnNo] = "UOM";
                                        break;
                                    case 8:
                                        xlWorkSheet.Cells[7, columnNo] = "Weight";
                                        break;
                                    case 9:
                                        xlWorkSheet.Cells[7, columnNo] = "Priority Code";
                                        break;
                                    case 10:
                                        xlWorkSheet.Cells[7, columnNo] = "Remarks";
                                        break;

                                }
                                chartRange = (Excel.Range)xlWorkSheet.Cells[7, columnNo];
                                chartRange.Font.Color = System.Drawing.Color.Black.ToArgb();
                                chartRange.Font.Bold = true;
                                chartRange.ColumnWidth = 20;

                            }

                            //Add Row Data

                            foreach (string row in lines)
                            {
                                rowNo++;

                                string[] strColData = row.ToString().Split('|');

                                if (strColData.Length > 0)
                                {
                                    for (int i = 0; i <= (strColData.Length - 1); i++)
                                    {
                                        xlWorkSheet.Cells[rowNo, i + 1] = strColData[i].ToString().Replace("\"", string.Empty);
                                        chartRange = (Excel.Range)xlWorkSheet.Cells[rowNo, i + 1];
                                        chartRange.WrapText = true;
                                        chartRange.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
                                    }
                                }
                            }

                            xlApp.Visible = false;
                            xlApp.DisplayAlerts = false;


                            xlWorkBook.SaveAs(Path.Combine(fileLocation, filename), Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                            xlWorkBook.Saved = true;
                        }
                    }
                }

            }
            catch (Exception ex)
            {

                sError = "Error InGetGLSExcelReports -->" + ex.Message;

            }
            finally
            {
                xlWorkBook.Close(true, Missing.Value, Missing.Value);

                if (sError != "")
                {
                    xlApp.Visible = false;
                    xlApp.DisplayAlerts = false;
                    xlWorkBook.Saved = false;
                }


                xlApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                GC.Collect();
                GC.WaitForPendingFinalizers();

                sReturn_Array[0] = sError;
                sReturn_Array[2] = Path.Combine(fileLocation, filename);

            }
            return sReturn_Array;
        }

        // Aug 10 2012 KA : AuditReport

        public string[] GenAuditExcelReport(string id, string sParams, string AcctName)
        {
            string[] sReturn_Array = new string[6];
            string sError = string.Empty;
            string filename = string.Empty;
            string fileLocation = string.Empty;
            string myexportfile = string.Empty;
            string sEventUpdateUrl = Properties.Settings.Default.EventUpdateUrl;

            string ConnStr = Properties.Settings.Default.ConnectionString;
            SqlConnection Conn = new SqlConnection(ConnStr);
            DataSet ds = new DataSet();

            string strData = string.Empty;

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range chartRange = null;
            object misValue = System.Reflection.Missing.Value;
            int rowNo = 8;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(Missing.Value);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet.Name = "ORBIS Audit Report";

            try
            {

                SqlCommand command = new SqlCommand("spAuditReport", Conn);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = 180;
                command.Parameters.AddWithValue("@Id", id);
                command.Parameters.AddWithValue("@ReportType", "EXCEL");

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds);


                if (ds.Tables.Count > 0)
                {

                    if (ds.Tables[0].Rows.Count == 0)
                    {
                        sError = "No Records Found";
                    }
                    else
                    {

                        //FileName

                        filename = "AuditRpt" + System.DateTime.Now.Millisecond + System.DateTime.Now;
                        filename = System.Text.RegularExpressions.Regex.Replace(filename, "/", "");
                        filename = System.Text.RegularExpressions.Regex.Replace(filename, " ", "");
                        filename = System.Text.RegularExpressions.Regex.Replace(filename, ":", "");


                        //Add Image

                        string _stPicture = Properties.Settings.Default.ImagePath;
                        xlWorkSheet.Shapes.AddPicture(_stPicture, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 230, 10, 150, 50);


                        //Add Report Title

                        if (string.IsNullOrEmpty(AcctName) == true)
                        {
                            xlWorkSheet.Cells[3, 2] = "Audit Report";
                        }
                        else
                        {
                            xlWorkSheet.Cells[3, 2] = "Audit Report - " + AcctName;

                        }


                        chartRange = (Excel.Range)xlWorkSheet.Cells[3, 2];
                        chartRange.Font.Bold = true;
                        chartRange.Font.Size = 26;
                        chartRange.Font.Name = "Arial";
                        chartRange.Font.Color = System.Drawing.Color.Red.ToArgb();


                        chartRange = xlApp.ActiveCell;
                        chartRange.EntireRow.WrapText = false;

                        //Add Report Columns

                        for (int columnNo = 1; columnNo <= 19; columnNo++)
                        {
                            switch (columnNo)
                            {
                                case 1:
                                    xlWorkSheet.Cells[7, columnNo] = "House Bill#";
                                    break;
                                case 2:
                                    xlWorkSheet.Cells[7, columnNo] = "Mawb#";
                                    break;
                                case 3:
                                    xlWorkSheet.Cells[7, columnNo] = "Error Code";
                                    break;
                                case 4:
                                    xlWorkSheet.Cells[7, columnNo] = "Error Description";
                                    break;
                                case 5:
                                    xlWorkSheet.Cells[7, columnNo] = "Days";
                                    break;
                                case 6:
                                    xlWorkSheet.Cells[7, columnNo] = "Orig Cntry";
                                    break;
                                case 7:
                                    xlWorkSheet.Cells[7, columnNo] = "Orig Office";
                                    break;
                                case 8:
                                    xlWorkSheet.Cells[7, columnNo] = "Dest Cntry";
                                    break;
                                case 9:
                                    xlWorkSheet.Cells[7, columnNo] = "Dest Office";
                                    break;
                                case 10:
                                    xlWorkSheet.Cells[7, columnNo] = "MOT";
                                    break;
                                case 11:
                                    xlWorkSheet.Cells[7, columnNo] = "Account";
                                    break;
                                case 12:
                                    xlWorkSheet.Cells[7, columnNo] = "Shipper";
                                    break;
                                case 13:
                                    xlWorkSheet.Cells[7, columnNo] = "Consignee";
                                    break;
                                case 14:
                                    xlWorkSheet.Cells[7, columnNo] = "Created";
                                    break;
                                case 15:
                                    xlWorkSheet.Cells[7, columnNo] = "Received";
                                    break;
                                case 16:
                                    xlWorkSheet.Cells[7, columnNo] = "Departs";
                                    break;
                                case 17:
                                    xlWorkSheet.Cells[7, columnNo] = "Arrives";
                                    break;
                                case 18:
                                    xlWorkSheet.Cells[7, columnNo] = "Delivered";
                                    break;
                                case 19:
                                    xlWorkSheet.Cells[7, columnNo] = "Total Documents";
                                    break;


                            }

                            chartRange = (Excel.Range)xlWorkSheet.Cells[7, columnNo];
                            chartRange.Font.Color = System.Drawing.Color.Black.ToArgb();
                            chartRange.Font.Bold = true;
                            chartRange.ColumnWidth = 20;
                        }


                        //Add Row Data
                        DataRow dr;

                        for (int row = 0; row <= (ds.Tables[0].Rows.Count - 1); row++)
                        {
                            dr = ds.Tables[0].Rows[row];

                            for (int col = 0; col <= (ds.Tables[0].Columns.Count - 1); col++)
                            {
                                chartRange = (Excel.Range)xlWorkSheet.Cells[rowNo, col + 1];

                                if (col == 0 || col == 1) //10/03/2012 KA: Audit Report (Excel was removing leading ZERO)
                                {
                                    chartRange.NumberFormat = "@";
                                }

                                xlWorkSheet.Cells[rowNo, col + 1] = dr[col].ToString().Trim();

                                if (col == 0) // House Bill #
                                {
                                    //Add a hyperlink to it.


                                    xlWorkSheet.Hyperlinks.Add(chartRange, sEventUpdateUrl + dr[col].ToString().Trim(), Type.Missing, "Click to go to Events Update site", dr[col].ToString().Trim());
                                }

                                chartRange.WrapText = true;
                                chartRange.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;

                            }
                            rowNo++;

                        }

                        xlApp.Visible = false;
                        xlApp.DisplayAlerts = false;

                        filename = filename + ".xls";
                        myexportfile = _DocPath + filename;

                        xlWorkBook.SaveAs(myexportfile, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                        xlWorkBook.Saved = true;
                        xlWorkBook.Close(true, Missing.Value, Missing.Value);
                    }
                }
            }


            catch (Exception ex)
            {

                sError = "Error in GenAuditExcelReport -->" + ex.Message;

            }
            finally
            {


                if (sError != "")
                {
                    xlApp.Visible = false;
                    xlApp.DisplayAlerts = false;
                    xlWorkBook.Saved = false;
                }


                xlApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                GC.Collect();
                GC.WaitForPendingFinalizers();

                sReturn_Array[0] = sError;
                sReturn_Array[2] = myexportfile;

            }
            return sReturn_Array;
        }

        ////kavita 12/14/2011 Ref No:21921 Title: Receiving Daily Orders (EDI)-- Cookie company --- send rejected orders to user --- commented out till other part finish

        public string[] GenCCRejectedOrders(string sAcctId, string sType, int sFileId)
        {

            string[] szReturn_Array = new string[6];
            string szConnStr = Properties.Settings.Default.ConStrJasFilesDB;
            string myexportfile = string.Empty;
            string szError = string.Empty;
            string strFilename = string.Empty;
            DataSet dsMainOut = null;
            string file = string.Empty;
            string strData = string.Empty;

            try
            {


                string szDoc = string.Empty;
                string[] szReadInfo = new string[11];

                GetRejectedDataForCC(szConnStr, sAcctId, sType, sFileId, ref dsMainOut);

                if (dsMainOut.Tables.Count > 0)
                {

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {

                        szError = string.Empty;
                        strData = dsMainOut.Tables[0].Rows[0]["Data"].ToString();
                        strFilename = dsMainOut.Tables[0].Rows[0]["FileName"].ToString();


                        file = CCWriteToString(dsMainOut.Tables[0], true, true);

                        myexportfile = _DocPath + strFilename;
                        File.WriteAllText(myexportfile, file.ToString());


                    }


                }
                else
                {
                    szError = "No Records Found";
                }
            }
            catch (Exception ex)
            {
                szError = "(GenCCRejectedOrders) " + ex.Message;
            }
            finally
            {

                szReturn_Array[0] = szError;
                szReturn_Array[2] = myexportfile;
                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }


        ////kavita 12/14/2011 Ref No:21921 Title: Receiving Daily Orders (EDI)-- Cookie company --- Get Rejected data for Cookie company 
        private void GetRejectedDataForCC(string szConnStr, string sAcctId, string sType, int iFileId, ref DataSet dsOut)
        {

            DataSet dsData = new DataSet();
            SqlConnection Conn = new SqlConnection(szConnStr);

            try
            {
                SqlCommand command = new SqlCommand("uspFILGetErrorData", Conn);
                command.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                command.Parameters.AddWithValue("@Acct", sAcctId);
                command.Parameters.AddWithValue("@Type", sType);
                command.Parameters.AddWithValue("@FileId", iFileId);

                adapter.Fill(dsData);
                dsOut = dsData;

            }
            catch (Exception Ex)
            {
                System.Diagnostics.Debug.WriteLine("Error In uspFILGetErrorData -->" + Ex.Message);

            }
            finally
            {
                Conn.Close();
            }

        }

        //kavita 12/14/2011 Ref No:21921 Title: Receiving Daily Orders (EDI)-- Cookie company 

        private string CCWriteToString(DataTable table, bool header, bool quoteall)
        {
            StringWriter writer = new StringWriter();

            CCWriteToStream(writer, table, header, quoteall);
            return writer.ToString();
        }

        //kavita 12/14/2011 Ref No:21921 Title: Receiving Daily Orders (EDI)-- Cookie company

        private void CCWriteToStream(TextWriter stream, DataTable table, bool header, bool quoteall)
        {

            //Write Column name
            if (header)
            {

                //Add Report Columns

                for (int columnNo = 1; columnNo <= 32; columnNo++)
                {
                    switch (columnNo)
                    {
                        case 1:
                            WriteItem(stream, "Supplier code", quoteall);
                            break;
                        case 2:
                            WriteItem(stream, "Supplier name", quoteall);
                            break;
                        case 3:
                            WriteItem(stream, "PO Number", quoteall);
                            break;
                        case 4:
                            WriteItem(stream, "Lot", quoteall);
                            break;
                        case 5:
                            WriteItem(stream, "Art.NO", quoteall);
                            break;
                        case 6:
                            WriteItem(stream, "Article Description", quoteall);
                            break;
                        case 7:
                            WriteItem(stream, "Qty", quoteall);
                            break;
                        case 8:
                            WriteItem(stream, "UM", quoteall);
                            break;
                        case 9:
                            WriteItem(stream, "Block", quoteall);
                            break;
                        case 10:
                            WriteItem(stream, "Qty Boxes", quoteall);
                            break;
                        case 11:
                            WriteItem(stream, "CBM", quoteall);
                            break;
                        case 12:
                            WriteItem(stream, "CRD Date", quoteall);
                            break;
                        case 13:
                            WriteItem(stream, "ETA", quoteall);
                            break;
                        case 14:
                            WriteItem(stream, "Delivery date", quoteall);
                            break;
                        case 15:
                            WriteItem(stream, "Kind of Transport", quoteall);
                            break;
                        case 16:
                            WriteItem(stream, "Place Transport_Depature", quoteall);
                            break;
                        case 17:
                            WriteItem(stream, "Place Transport_Destination", quoteall);
                            break;
                        case 18:
                            WriteItem(stream, "INCO_Ceva", quoteall);
                            break;
                        case 19:
                            WriteItem(stream, "Customer Name", quoteall);
                            break;
                        case 20:
                            WriteItem(stream, "Customer Number", quoteall);
                            break;
                        case 21:
                            WriteItem(stream, "Delivery Country", quoteall);
                            break;
                        case 22:
                            WriteItem(stream, "Confirmed by supplier", quoteall);
                            break;
                        case 23:
                            WriteItem(stream, "LineNumberID", quoteall);
                            break;
                        case 24:
                            WriteItem(stream, "OrderAnouncedToCEVA", quoteall);
                            break;
                        case 25:
                            WriteItem(stream, "AnnCevaETD", quoteall);
                            break;
                        case 26:
                            WriteItem(stream, "AnnCevaETA", quoteall);
                            break;
                        case 27:
                            WriteItem(stream, "Responisible", quoteall);
                            break;
                        case 28:
                            WriteItem(stream, "Containernumbers", quoteall);
                            break;
                        case 29:
                            WriteItem(stream, "OrderProcess", quoteall);
                            break;
                        case 30:
                            WriteItem(stream, "PO_Datemade", quoteall);
                            break;
                        case 31:
                            WriteItem(stream, "PO_DateSendToSupplier", quoteall);
                            break;
                        case 32:
                            WriteItem(stream, "Error Message", quoteall);
                            break;


                    }
                    if (columnNo < 32)
                    {
                        stream.Write(',');
                    }

                }


            }

            //Write data

            foreach (DataRow row in table.Rows)
            {
                stream.Write(stream.NewLine);

                for (int i = 0; i < table.Columns.Count - 1; i++)
                {
                    WriteItem(stream, row[i], quoteall);

                    if (i < table.Columns.Count - 1)
                    {
                        stream.Write(',');
                    }

                }
            }

            stream.Close();
        }

      
        public string[] GenCCOrdersReport(string strAccount, string strType, string strFileId)
        {

            string[] szReturn_Array = new string[6];
            string szConnStr = Properties.Settings.Default.ConStrJasFilesDB;
            string myexportfile = string.Empty;
            string CCOdersFile = string.Empty;
            string szError = string.Empty;
            string strFilename = string.Empty;
            DataSet dsMainOut = null;
            string file = string.Empty;
            string FilePath = string.Empty;

            try
            {


                GetDataForCCOrdersReport(szConnStr, strAccount, strType, strFileId, ref dsMainOut);

                if (dsMainOut.Tables.Count > 0)
                {

                    if (dsMainOut.Tables[0].Rows.Count == 0)
                    {
                        szError = "No Records Found";
                    }
                    else
                    {

                        szError = string.Empty;
                        strFilename = dsMainOut.Tables[0].Rows[0]["FileName"].ToString();
                        file = dsMainOut.Tables[0].Rows[0]["FileData"].ToString();
                        FilePath = dsMainOut.Tables[0].Rows[0]["FilePath"].ToString();
                        CCOdersFile = FilePath + strFilename;
                        File.WriteAllText(CCOdersFile, file.ToString());

                    }

                    myexportfile = CCOdersFile;
                }
                else
                {
                    szError = "No Records Found";
                }
            }
            catch (Exception ex)
            {
                szError = "(GenCCOrdersReport) " + ex.Message;
            }
            finally
            {

                szReturn_Array[0] = szError;
                szReturn_Array[2] = myexportfile;
                if (!(dsMainOut == null))
                {
                    dsMainOut.Dispose();
                }

            }
            return szReturn_Array;
        }




        //kavita 12/14/2011 --ticket# 21923 Title: Daily Shipping Report  -- Get data for CCOrderReport (Daily shipment report)


        private void GetDataForCCOrdersReport(string szConnStr, string sAcctId, string sType, string strFileId, ref DataSet dsOut)
        {

            DataSet dsData = new DataSet();
            SqlConnection Conn = new SqlConnection(szConnStr);

            try
            {
                SqlCommand command = new SqlCommand("uspFILOUTGetFiles", Conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Acct", sAcctId);
                command.Parameters.AddWithValue("@Type", sType);
                command.Parameters.AddWithValue("@Fileid", strFileId);


                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dsData);
                dsOut = dsData;

            }
            catch (Exception Ex)
            {
                System.Diagnostics.Debug.WriteLine("Error In GetDataForCCOrdersReport -->" + Ex.Message);

            }
            finally
            {
                Conn.Close();
            }



        }

        //TW: 06/13/2012 Exiros EDI Project
        private DataSet GetDataForExirosEDIReport(string fileID, string type, string account, string supplierid)
        {

            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection(Properties.Settings.Default.ConStrJasFilesDB);
            try
            {
                SqlCommand cmd = new SqlCommand("uspFILGetEDIReportData", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Acct", account);
                cmd.Parameters.AddWithValue("@Type", type);
                cmd.Parameters.AddWithValue("@FileID", fileID);

                if (!string.IsNullOrEmpty(supplierid))
                    cmd.Parameters.AddWithValue("@SupplierID", supplierid);

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                adapter.Fill(ds);

                return ds;

            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Error In GetDataForExirosEDIReport -->" + ex.Message);
                return null;
            }

        }

        public string[] GetExirosEDIReport(string[] InfoIn, string filePath)
        {
            string[] szReturn_Array = new string[6];
            string myExportFile = string.Empty;
            string error = string.Empty;
            string fileName = "ExirosOrders_" + DateTime.Now.ToString("yyyy-MM-dd hh_mm_ss tt");

            Excel.Workbook oBook;
            object misValue = Missing.Value;

            Excel.Application oExcelApp;
            oExcelApp = new Excel.ApplicationClass();

            oBook = oExcelApp.Workbooks.Add(misValue);

            fileName = fileName + ".xls";
            myExportFile = Path.Combine(_DocPath, fileName);

            try
            {
                DataSet ds = GetDataForExirosEDIReport(InfoIn[9], InfoIn[1], "2182", InfoIn[14]);

                oExcelApp.Visible = false;
                oExcelApp.DisplayAlerts = false;


                oBook = BuildExirosReportWorkBook(ds, oBook, myExportFile);



                oBook.Saved = true;
                oBook.Close(true, misValue, misValue);

            }
            catch (Exception ex)
            {
                error = "[Exiros EDI] : -" + ex.Message;
            }
            finally
            {
                oExcelApp.Quit();
                releaseObject(oExcelApp);
                szReturn_Array[0] = error;
                szReturn_Array[2] = myExportFile;
            }

            return szReturn_Array;
        }

        private Excel.Workbook BuildExirosReportWorkBook(DataSet ds, Excel.Workbook oBook, string fileName)
        {
            string rptType = string.Empty;
            string pix = string.Empty;
            Excel.Sheets oSheets;
            Excel.Worksheet SuccessSheet;
            Excel.Worksheet RejectSheet;
            Excel.Range oCells;
            Excel.Range range;
            object misValue = Missing.Value;

            SuccessSheet = (Excel.Worksheet)oBook.Worksheets.Add(misValue, misValue, misValue, misValue);
            RejectSheet = (Excel.Worksheet)oBook.Worksheets.Add(misValue, misValue, misValue, misValue);
            SuccessSheet.Name = "Accepted Orders";
            RejectSheet.Name = "Rejected Orders";


            oSheets = oBook.Worksheets;

#if  RELEASE
            //pix = "D:\\etrack_net\\JasOrbisSL\\wsJas\\jasorbis1.png";
            //SuccessSheet.Shapes.AddPicture(pix, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 200, 10, 150, 50);
            //RejectSheet.Shapes.AddPicture(pix, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 200, 10, 150, 50);
#endif

            if (ds.Tables.Count > 0)
            {
                DataTable dt_success = ds.Tables[0];
                DataTable dt_errors = ds.Tables[1];



                if (dt_success.Rows.Count > 0)
                {
                    SuccessSheet.Cells[3, 2] = SuccessSheet.Name;

                    DumpDataForExirosReports(SuccessSheet.Name, dt_success, dt_errors, SuccessSheet.Cells);

                }

                if (dt_errors.Rows.Count > 0)
                {
                    RejectSheet.Cells[3, 2] = RejectSheet.Name;

                    DumpDataForExirosReports(RejectSheet.Name, dt_success, dt_errors, RejectSheet.Cells);
                }



                foreach (Excel.Worksheet o in oSheets)
                {
                    range = (Excel.Range)o.Cells[2, 6];
                    range.Font.Color = System.Drawing.Color.Blue.ToArgb();
                    range.Font.Bold = true;
                    range.Font.Size = 26;
                    range.Font.Name = "Arial";
                    //range.Font.Color = System.Drawing.Color.Red.ToArgb();
                    range.EntireRow.WrapText = false;
                }

            }

            oBook.SaveAs(fileName);

            return oBook;
        }

        private void DumpDataForExirosReports(string rptType, DataTable dtAccepted, DataTable dtRejected, Excel.Range range)
        {
            DataRow dr;
            int i = 0;

            // Create Header Row      
            for (int colNo = 1; colNo <= 23; colNo++)
            {
                switch (colNo)
                {
                    case 1:
                        range.Cells[7, colNo] = "PO Date";
                        break;
                    case 2:
                        range.Cells[7, colNo] = "Supplier ID";
                        break;
                    case 3:
                        range.Cells[7, colNo] = "Supplier Name";
                        break;
                    case 4:
                        range.Cells[7, colNo] = "PO Number";
                        break;
                    case 5:
                        range.Cells[7, colNo] = "CONCATENAR";
                        break;
                    case 6:
                        range.Cells[7, colNo] = "Line Number";
                        break;
                    case 7:
                        range.Cells[7, colNo] = "Item Number";
                        break;
                    case 8:
                        range.Cells[7, colNo] = "Item Description";
                        break;
                    case 9:
                        range.Cells[7, colNo] = "Quantity Ordered";
                        break;
                    case 10:
                        range.Cells[7, colNo] = "Unit Price";
                        break;
                    case 11:
                        range.Cells[7, colNo] = "Currency";
                        break;
                    case 12:
                        range.Cells[7, colNo] = "Extended Price";
                        break;
                    case 13:
                        range.Cells[7, colNo] = "Consignee ID";
                        break;
                    case 14:
                        range.Cells[7, colNo] = "Consignee Name";
                        break;
                    case 15:
                        range.Cells[7, colNo] = "Required By Date";
                        break;
                    case 16:
                        range.Cells[7, colNo] = "Buyer ID";
                        break;
                    case 17:
                        range.Cells[7, colNo] = "Buyer Name";
                        break;
                    case 18:
                        range.Cells[7, colNo] = "Origin Country";
                        break;
                    case 19:
                        range.Cells[7, colNo] = "Shipping Mode";
                        break;
                    case 20:
                        range.Cells[7, colNo] = "Origin Port / Airport";
                        break;
                    case 21:
                        range.Cells[7, colNo] = "Destination Port / Airport";
                        break;
                    case 22:
                        range.Cells[7, colNo] = "Incoterm";
                        break;

                }

          
                range.Cells[7, 23] = "Order  Processing Notes";
            }


            //Fill in the Data
            if (rptType == "Accepted Orders")
            {
                dr = dtAccepted.Rows[0];
                WriteToCell(dr, range);
            }
            else
            {

                if (dtRejected.Rows.Count != 0)
                {
                    foreach (DataRow row in dtRejected.Rows)
                    {
                        WriteToCell(row, range);
                        range.Cells[8 + i, 23] = dtRejected.Rows[i]["ErrMsg"].ToString();
                        i++;

                    }
                }
            }
        }


        private void WriteToCell(DataRow dr, Excel.Range range)
        {
            int rowNo = 7;
            string data = string.Empty;

            data = dr[0].ToString(); // ;
            string[] lines = data.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);

            if (lines.Length > 0)
            {

                foreach (string line in lines)
                {
                    rowNo++;
                    string[] colData = line.Split(';');

                    if (colData.Length > 0)
                    {
                        for (int i = 0; i <= (colData.Length - 1); i++)
                        {
                            range.Cells[rowNo, i + 1] = colData[i].ToString();
                            range.WrapText = false;
                            range.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
                        }
                    }
                }
            }
        }


    }
}
